Convert File Before Importing
Mar 12, 2008
All, I have code to import multiple text files into multiple tables in access 2003 There are about 15 tables It works but I have two problems. 1st I was converting the files from another source to text and placing them into a folder where I created import specification files. The problem is I need to automate the conversion part. How do I use VBA code to look at the file and add the extension .txt so that the program can find the file in the code. Here's part of my code. I didn't list all 15
'Import Text files into corresponding tables
DoCmd.TransferText acImportFixed, "PhoneNoProblems Import Specification", _
"PhoneNoProblems", "X:DB_Working 017_ErrorsTextFilesPhoneNoProblem s.txt", True
DoCmd.TransferText acImportFixed, "StrangeZipcodes Import Specification", _
"StrangeZipcodes", "X:DB_Working 017_ErrorsTextFilesStrangeZipcode s.txt", True
In the textfiles folder, before the filename with .txt exist I have to add the .txt How do I add it automatically before the import?
2nd I need a cleanup routine to remove the additional lines in the text files like: list and sort which can appear randomily throughout the text files.
I hope I've explained this so you can help me
Thanks
View Replies
ADVERTISEMENT
Mar 14, 2013
I have a spreadsheet of generators with associated data: Make, Model, etc.
When I build the tables for the Make, each make will have it's own PK. Is there a good way to update the spreadsheet, replacing the Make for the PK other than using the find and replace function in excel (or update query in Access).
Example:
tblGeneratorMake
GeneratorID (PK)
GeneratorMake
John Deere has PK of 1
Kohler has PK of 2
If my spreadsheet has a list of Kohler generators, I need to change "Kohler" to "2".
View 14 Replies
View Related
Jul 18, 2007
Hi,
I want to make some changes in .MDB file and i do not have it. I have only .MDE file.. Could you please let me know how i can convert .MDE file into >MDB file so can change tabelsa nad reports...
Many Thanks
Nitesha
View 1 Replies
View Related
Dec 30, 2011
i want to convert accdb file to an exe file that not display any design view in other words i want the user watch only the forms and handle with the application as a program.
View 2 Replies
View Related
Nov 14, 2006
I want to put a File Browser on a form so my users can browse their desktop for the correct TXT file they want to be imported into the database. At first, I did nto think this would be hard but it seems as though it is some what of a challenge.
Any help is appreciated.
Thank You,
Marc
View 1 Replies
View Related
Jun 28, 2005
I have a txt file that includes X and Y variables like below, with a comma seperating the x from the y.
1.5,2
1.9,30.25
100.00,5.23
4.16,12.3
10.23,14.5
..
....
..... up to an unspecified number
how can I import the txt file data and place that data into a table.
A GPS device creates the txt file that will be placed in a folder.
thank you in advance
View 5 Replies
View Related
Jan 29, 2013
I am trying to convert a CSV file that I receive from UPS to a simpler table. I have over 3000 entries from 25 different locations and I need to evaluate the shipping cost. Is there a way in Access to convert the file as attached or should I be trying to use Excel?
View 2 Replies
View Related
May 5, 2005
Hi all,
I am trying to upload a .csv file in a table using the tRansfertext method in vba. I have atext field having alphanumeric values. The upload is excluding the some values and generating a new error table. How do I get rid of this error.
Do I have to use a specification name. I have new .csv file to be uploaded every week, How do I work on the spec file ?
Any answers please..
Thanks
Nira.
View 8 Replies
View Related
Jul 18, 2007
hi,
i wnat to convert .mde file into .mdb file as i want to make some changes into tabels and reports..
please let me know how to do this.
Many Thanks.
Nitesha
View 1 Replies
View Related
Oct 21, 2013
How can I convert a web Access file to desktop aplication? I've created it from a model from Office.com, but I need create code in vba and it don't permit.
The use of file is only in desktop.
View 3 Replies
View Related
Jul 2, 2014
The file was converted from excel. It is in Datasheet view. I select the first column and clip on the Ascending choice under the Home Tab. It works but leaves a large gab of blank rows. I go to the Database Tools tab and check Compact and Repair Database. The file returns to the original unorganized list.
View 1 Replies
View Related
Aug 7, 2006
We have received a zip file from a new client containing several thousand loans. The problem is that when I open the file as text all of the data is in one "field" meaning instead of going across the data goes down.
loan type,
loan number,
origination date,
loan term,
lo type,
etc....
Then it starts over with the next loan. There are over 17,000 lines.
How can I import the text file and have the loans go across. Even when I have imported the file using comma delimited it still imports them going down. Even if the comma delimited would work, I would still have issues because Access would not know when the new loan began to move it to the next row.
Any thoughts or suggestions? Sorry for my rambling.
View 6 Replies
View Related
May 27, 2005
Hey,
I have a text file i wish to link up to one of my tables. The problem is that even if i try to import data from that file i get "Text file specifikation field separator matches decimal separator or text delimiter." message and after that one an err that the file cant be imported. Does anyone know why?
Thats the sample of my file:
'PV','045.302','KROG VEN S PNEU POG','JOHN-VALVE + AIR TORQUE','JV-9301NC-BW + AT051DA','','','','15','8','VANI','','1.4408','PTF E','ZRK'
View 4 Replies
View Related
Oct 26, 2006
Hi! There was a problem that one of the members had a while back, and the solution was never posted. Now, I am having the exact same problem. Here's the link.
http://forums.aspfree.com/microsoft-access-help-18/importing-multiple-text-files-125634.html
When I compile, it works ok, but then when I click my command button, it says I can't import this file. Any ideas?
KellyJo
View 1 Replies
View Related
Aug 25, 2011
I receive a .txt file daily and want to import the information into a table. My problem is that the currency values in the .txt file do not contain a decimal. For instance $7.12 will come in showing as 712. How do I get Access to recognize it with the decimal?
View 2 Replies
View Related
May 31, 2005
Hi, I have an xml which fields of text and some of numbers.
When I do: File --> Get External Data --> Import; I'm able to import the file.
The problem is that not all the data is entered in the fields. The colums containing the text are excluded.
While importing, I did choose the option "Structure and Data", so it should work.
Any suggestions on to why it is doing that?
Thanks.
View 2 Replies
View Related
Aug 1, 2005
hi, i have struggled to import some data from an excel file into an sql database..
i have used phpmyadmin and certain code snippets but have failed miserably..
basically my excel file has lots of data, and within each cell, each bit of data is in single quotes...
eg.
'jonathan' '23' 'hardman' 'cheese'
there are no headings in the excel file (as in column or row titles) the data is just raw.
once i have made the table (with the appropriate fields and datatypes for the csv file) how can i import that data into a table using ms access???
View 2 Replies
View Related
Nov 3, 2005
I have a dbf file that I need to somehow get the table data out of, and into access, excel, or some other usable format. I searches the MS Knowledge Base and read several articles on updating the Jet 4.0 drivers (up to date) and updating Foxpro ODBC drivers, etc. I also searched these forums, and I have yet to find any solution that works.
I think this dbf file might be Foxpro, but I'm not sure. I don't know anything about Foxpro. I was getting an error message "unexpected error from external database driver (8961)", until I read an MS article stating that I should change the name of the Borland folder to BorlandOld and try re-importing. Now Access 2003 just says: "external table not in the specified format".
View 3 Replies
View Related
Apr 3, 2006
Loss of Primary Key when importing csv file
Hi All,
We are submitting an Access db table with ID as primary key to an outside company for processing. We export it as csv file and that's how it comes back.
After importing it back, we noticed that our IDs, which previously had 'gaps' as a result of deleted records, had been changed in to a new continuous sequence (Autonumbered). This, of course, completely destroys our database since related tables depend on IDs being unchangeable.
We ***suspect*** that the IDs are being destroyed during the process of re-importing the csv file. We are using the Access import wizzard.
Access msg: "Access recommends that you define a Primary Key for your new table". We then are given the following options:
1Let Access ADD primary key
2Choose my own primary key
3No primary key
During Option 1, the ID column is displayed and high-lighted. But we do NOT want that option since it offers to ADD a primary key, whereas we already have one (ID). At most, we want to 'declare' or 'define' a primary key, but we do not want Access to ADD one.
So we try Option 2, hoping that this will give us the chance to CHOOSE ID as our primary key. But the moment we select that option, the ID column disappears. We are not given the chance to select ID as our primary key.
So we try Option 3, hoping to re-instate ID as primary key as soon as we have an Access table again. But the moment we select this option, the ID column disappears.
-----------------------
If we GO THROUGH with Option 1, we notice that Access has renumbered our ID, the IDs of all our 'deleted' records have been re-assigned. Or so it appears.
If we GO THROUGH with Option 3, the resulting table does not contain an ID column at all.
Of course, the damage may have been done by the outside company which processed our file, but if so, we have to prove it, and we have to take measures to avoid it.
Any comments and advice?
Thanks.
Adrian
View 3 Replies
View Related
Jul 6, 2005
So I have had Access for all of one day now. I am trying to import an excel file into access as a table. I successfully imported the first file but the second file gives me an error that says "An error occurred trying to import file ____. The file was not imported." What are the possible reasons this error has occurred? Both excel files I am importing are in the same format, so I am confused as to why one worked but the other did not. Thanks for any help.
View 2 Replies
View Related
May 25, 2006
I work for a company that has a mainframe application that produces "outgoing" files. They are downloaded to a server and converted to ASCII (all at one time) and then ftp'ed to various clients. We are converting to a client server application and I am in charge of verifying that the mainframe application files (converted) are EXACTLY the same as the server application files. I came up with a process of loading the files to access databases and then with a series of queries and reports produce a list of exactly what doesn't match, which record, what positions and display the two fields that are mismatching.
My problem is that when I try to load the Mainframe file (note that it has already been converted and "eyeball to eyeball" the files are alike) using the "new" / import functions, Access rejects the file because it is greater than 65000 bytes. If I cut the file down to under 65k bytes it loads fine. I can load a 500,000 byte file that was created on the server in the first place.
Any suggestions? I can't change any of the processes that create the files. But I can change copies of the files so that I might be able to get them to load so I can verify that the data inside is the same. I'll let the geeks figure out how to fix the file/record control stuff.
(wouldn't have this problem if I was back on a mainframe!!!!)
Mac
View 3 Replies
View Related
Feb 21, 2014
I am trying to import a csv file into a table... it works fine however it put all of the row in just one column ...
DoCmd.TransferText TransferType:=acImportDelim, TableName:="tblTempImport", _
Filename:=CurrentProject.Path & "/xxx.csv", HasFieldNames:=False
And i end up with tblTempImport only having one column F1....
View 14 Replies
View Related
Oct 29, 2012
I am trying to set up an access button to go to a website, download its source code and import that txt file into a table so it can be parsed. Well, when I go to import this text file, it imports that data in a weird inconsistent order. The problem is I need the order to read exactly how it is from top to bottom, since the numbers I'm parsing from the code need to correspond to an XL Spreadsheet (which also gets imported).
I have the code set up, and everything is doing what it needs to do perfectly, EXCEPT this import. I know it's something stupid, but I just don't know what! I have everything being imported into a text file with an arbitrary delimiter that doesn't appear anywhere in the source code. Is there a way I pull this code in line by line in order into one field with X amount of rows so I can just run queries to pull the numbers I need?
View 13 Replies
View Related
Oct 3, 2012
Is there a way to automatically skip row 1 of a CSV file when importing? Row 1 contains a header with filename, date created, period covered, total record count, etc., and then Row 2 contains the column names.
View 5 Replies
View Related
Mar 8, 2006
I have a problem whereby I want to import an Excel file into my Access db but certain characters like >< it doesn't accept. what other options do I have?
View 1 Replies
View Related
Feb 10, 2005
Hi
My question is: how do I set up the table to minimize redundant data. I have several fields that match the column headings in Excel, so the data can be imported, but fields like user name, pick slot, batch # all get redundant every time I import. My file is getting unnecessarily large. I know splitting the one table into many is the right thing to do, but don't have a clue how to import the data than. any help
Thanks
View 10 Replies
View Related