# Access Text Import Question



## rconverse (Sep 8, 2007)

Hello,

I am trying to create an import spec for a tab delimmited file, however, the spec doesn't really "take" in the code. The file can be manually imported easily, but when I try to attach it to a "on click" evevnt, the table imports like junk. Any suggestions? I have checked my three reference books as well as the online help and can't figure out what is wrong.

Also, I am trying to repeatedly copy a table and add a date onto the end. So for example, the query looks like this:

Select tblActiveProds.* INTO tblActiveProds '" & strTblDate & "' FROM tblActiveProds;"

I can't get the date to concactinate on the end. Any suggestions there would be helpful as well. I tried the rename command as well, but found the same issue.

Thank you,
Roger


----------



## Rockn (Jul 29, 2001)

What's not working with the import? It looks like you aren't defining the fields that the delimited data is being imported into. If the filed you are importing into is set as a date/time field the concatenated data won't import.


----------



## rconverse (Sep 8, 2007)

Hello and thank you for the reply!

The portion of the import that is not working is the fact that the columns are not separating. Again, if I were to imprt the file manually, I can import withoug any issues. However, when I throw that into code DoCmd.Transfer text Deleim...etc, I can get the file to import, however not correctly. Instead of having like 15 different column headings, I receive three. Therefore, the first column is like 70 characters long and actually contains the remaining amount of columns left (Basically instead of getting 15 columns in the table, I get three and the first column contains info from columns 1 - 13. 

The concatination is a little different. In an attempt to simplify, let's suppose I have a file I have to import every week named "week". Instead of overwriting that file every week, I want to run a make table query that will copy tblWeek to tblWeekMMDDYYY for historical purposes where MMDDYYYY is a string variable of the current date in MMDDYYYY format. 

I hope that makes a little more sense. I am a beginner to intermediate, so I sincerely apologize if I am wasting time by asking dumb questions or not being clear. I just started this new job, so am trying to expand my skills to impress my new employers. 

Thank you very much,
Roger


----------



## OBP (Mar 8, 2005)

When you imported the Text file manually did you save an Import Specification of how you did it?
If so include that in the VBA Import command line.


----------



## rconverse (Sep 8, 2007)

Absolutely. I saved the import spec and reference it in my DoCmd.TransferText... code. Again, for some reason it just doesn't recognize that the file is tab delimmited. It will pull in the file, but most fields are pulled in together. Instead of 15 columns or fields, I get three, with column or field one actually being like 13 fileds.

Thanks,
Roger


----------



## OBP (Mar 8, 2005)

Roger, if the problem persists with the Import I may be able to provide you with a work around. Does the First field which contains the 13 extra fields still have the commas separating the fields?
As to the date concatenation, use something like this
dim filedate as string
filedate = "week " & mid(date(), 4,2) & left(date(),2) & right(date(), 4)

I can't remember whether The VBA Editor uses Date() or changes it to just Date.


----------



## rconverse (Sep 8, 2007)

I found a workaround for my import issue, but no, the fields were not still separated.

I do not have an issue with the date format. i format the date to mmddyyyy format as strFileDate using Format(Date(), "mmddyyyy")

The issue is I want to copy a table every week and tack on strFileDate in the query.

So the query looks something like this:

strSql = "Select tblActiveProds.* INTO tblActiveProds " & strTblDate & " FROM 
tblActiveProds;"

I would like to create a table that is then tblActiveProds09072007, but I can't get that part to work.

Thanks,
Roger


----------



## OBP (Mar 8, 2005)

Have you tried concatenating the file name and formatted date prior to including it in the SQL statement?


----------



## Rockn (Jul 29, 2001)

For every import you are looking to create a new table? If you are creating tables use the MAKE TABLE statement. It is confusing as to what you are actually trying to accomplish here.


----------



## rconverse (Sep 8, 2007)

There were two separate issues. One was trying to import a tab delimitted file into a table using an import spec, for which I found a workaround.

The other issue was trying to use a make table query. I figured out the workaround for that as well. Instead of trying to add strTblDate to the end of the table name in the query, I added the table name to strTblDate first. Therefore, my new code looks like this:

strTblDate = tblLastWeekActiveProds & Format(Date(), "mmddyyyy")

strSql = "SELECT tblActiveProdsLastWeek.* INTO " & strTblDate & " FROM tblActiveProdsLastWeek;"

DoCmd.RunSQL (strSql)

Worked like a charm.

Thanks for all the replies though. Sorry if I was confusing.


----------



## rconverse (Sep 8, 2007)

OBP said:


> Have you tried concatenating the file name and formatted date prior to including it in the SQL statement?


I did and that is what worked. I wish I would have seen your post yesterday though.

Thanks!!


----------



## JerA (Oct 11, 2007)

I'm having the same issue with almost everything ending up in the first column, when importing via VBA using the saved import spec. If I reimport manually using the same saved import spec, it imports fine. What was the work-around that works?

(I'm using Access 2003 on XP Pro, in case it matters.)


----------



## rconverse (Sep 8, 2007)

Unfortunately, the workaround was not in the code, but an issue with the file. Basically, I was receiving a file without a .tab extension. When I added the .tab extension, it worked. I also recently found another ripple with Access 2003. I receive a file from our mainframe every morning. Although the file is attached as "filename.xls", it really is a .tab file. When I tried to import a .xls file using an Import Spec, it errors out. I have to open and save that file as a .tab file everytime for the import spec to work correctly. 

Not sure if that will help you, but you may want to look closer at the file you are importing.

What type of file are you trying to import? Tab, comma delimmetted? If you can attach a portion of the file, I can take a look and attempt to identify the issue.

Thanks,
Roger


----------



## JerA (Oct 11, 2007)

Here's a sample of the tab-delimited file, w/filename format "filename.txt" - I saved the import spec w/all the tabs, but have to manually reimport to get everything to fall into the correct fields, since importing it with the rest of the files through a VBA script dumps most of it into the first field.

VSIGGAGS20371 15G0 -1 0 Group SCR 99
VSIGGAGS20371 15G0 -1 1 If All 
VSIGGAGS20371 15S0 0 2 SPEC SCR 1030
VSIGGAGS20371 15S0 0 3 IF EFFDT EQ00000000:99999999
VSIGGAGS20371 15S0 0 4 ANDShipSt EQME/VT
VSIGGAGS20371 15S0 0 5 AndUsage EQ&NC8
VSIGGAGS20371 15S0 0 6 Assign NCOMsgTxt NC8 OVERRIDE N/A FOR MAINE,VERMONT
VSIGGAGS20371 15S0 0 7 Assign ErrPnd Y
VSIGGAGS20371 15S0 1 8 Spec SCR 01023
VSIGGAGS20371 15S0 1 9 If EffDt EQ20021029:99999999
VSIGGAGS20371 15S0 1 10 AndChrgBIC GQ00000215811
VSIGGAGS20371 15S0 1 11 AndShipBIC GQ00000215811
VSIGGAGS20371 15S0 1 12 AndUsage NE&FA9/YS5
VSIGGAGS20371 15S0 1 13 Assign SysOpt NS1
VSIGGAGS20371 15S0 1 14 Assign SysOpt NA7


----------



## rconverse (Sep 8, 2007)

I think if the file is tab delimitted, it needsd to have a .tab extension, not a .txt. If you receive or are starting with a file with a .txt extension and trying to import with anything other than a fixed postion import spec, you will have issues. In my apps, a .txt file is a fixed position file. Hope that helps!

Thank you,
Roger


----------



## OBP (Mar 8, 2005)

JerA, you said "since importing it with the rest of the files through a VBA script dumps most of it into the first field."
What doesn't it put in the first field?
Do you want some VBA to convert the data?


----------

