Importing a text file... 1

[benlinkknilneb]

Hey all,

I'm Importing a comma-delimited text file into my Access2000 db. The problem I'm running into is that the TransferText method decided all my fields should be numbers... including one with text in it. Because of this, I got a table with the errors listed and a lot of records in my import totally wiped out (because text can't convert to number). Is there a way I can tell Access what data types there are in code? My file comes in like this:

20030509,3,08,F5TE8505DA,5,14,20030506,1,01               
20030509,3,08,F5TE8505DA,10,41,20030506,1,01              
20030509,3,08,F5TE8505DA,1,42,20030506,1,01               
20030509,3,08,F5TE8505DA,1,43,20030506,1,01

The error is in the 4th field, which is the "Part Number". Access tries to convert it to a Long Integer, and of course, all my records lose the data because there are characters in there.

If I import this manually, it works fine... but automating it with TransferText isn't working.

Ben

 

[psemianonymous]

Import it manually, set all the fields to text in the wizard, and save the import specification. Then when using TransferText, use that import specification you have saved.


The other option is to import into a pre-existing table which has fields already set to "Text". I don't know if this is feasible, and it may still require that you still use a specific import specification to get the data how you like it.

Go with my first paragraph.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[jfgambit]

Create a Specification Name for the import:

The specification name for the set of options that determines how a text file is imported, exported, or linked. For a fixed-width text file, you must either specify an argument or use a schema.ini file, which must be stored in the same folder as the imported, linked, or exported text file.
You can use the Import or Link Tables subcommand of the Get External Data command or the Export command on the File menu to create a specification for a particular type of text file. For example, a delimited text file that uses tabs to separate fields and has an MDY format for dates. When you click one of these commands and select a type of text file to import, export, or link, the Import Text Wizard, Export Text Wizard, or Link Text Wizard runs. You can click the Advanced button in the wizard and define and save a specification in the dialog box that appears. You can then type the specification name in this argument whenever you want to import or export the same type of text file.
You can import, export, or link delimited text files without typing a specification name for this argument. In this case, Microsoft Access uses the defaults from the wizard dialog box. Microsoft Access uses a predetermined format for mail merge data files, so you don't ever need to type a specification name for this argument when you export these types of files. You can use import/export specifications with HTML files, but the only part of the specification that applies is the specification for data type formatting.


In the help files do a search for TranferText method, there are examples.


"I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?"

 

[benlinkknilneb]

Thanks, foolio, you 'da man!

Ben

 

[mgerbs]

Hi Guys:

I am attempting to to a little bit more with this import functionality than what has been said already. I am currently trying to import a text file, for which using the manually created specification name would work, but the file name could be different on each import, so I need to be able to open a "Open File" window to retrieve the user selected file name and then use that file and path to import. Also, the import specification might be different based on the source of the file, so I have to allow for different specifications. Is there any way that I can do this through code? If so, how?

Mike

 

[psemianonymous]

I'd recommend, if the user is able to import a different type of file, that you not allow them to import all the different file types in the same place. If you use menu items, make a different form for each import type and a different menu item for each type.

Combining all your text imports into "Import Central" is a good idea, until you user mischooses which type of import he's doing, then the whole thing crashes down. Hard.


As for the specifics, use the default windows File/Open dialog box. There are FAQs here covering that subject. And as for the rest--well, I covered that above.