Question Problems Importing text file (1 Viewer)

[Martyh]

Here is the import statement I'm using:


DoCmd.TransferText acImportDelim, "IT315 Import Specification_txt", _
"IT315", "D:\4533 Hires Rehire Status Change\IT315.txt"

I can manually transfer the data ie thru File --> Get External Data etc but I can't seem to get the above statement to work --- with the same specification!!

the details are in the attached doc file.

Any help is appreciated

Marty Habicht

 

[Estuardo]

G'd morning,
Can you post a copy with just a few lines of the file your working on? Looks to me that the separator "|" makes empty columns in the automatic process. When you do it by hand you skip those columns. am i right?

 

[Martyh]

Ok you maybe right but how do I solve the problem? I'm using the exact same spec whether I do it manually or under automation!!

I've already ripped out half my hair, now I am looking at going bald!! :banghead:

 

[Martyh]

I'm not sure which file you want. I've enclosed everything I think!!

Marty

 

[Estuardo]

Martyh,
The documents you zipped are pictures. No body will create a table and fill it by hand to test it. If you can provide the notepad file with few lines enough to test will be easier to assist you.

 

[Martyh]

Ok now I get it !!



Here is a few lines of my file:
Call it "IT315.txt"
----------------cut here------------------------------------
Table: PA0315
Displayed Fields: 4 of 4 Fixed Columns: 3 List Width 1023
---------------------------------------------
| |PERNR |ENDDA |BEGDA |LSTAR |
|--------------------------------------------|
| |00272043 |9999/12/31|2013/04/14|400 |
| |00276545 |9999/12/31|2013/04/28|400 |
| |00280828 |9999/12/31|2013/04/11|400 |
| |00311383 |9999/12/31|2013/04/14|400 |
| |00503002 |2013/05/23|2013/04/26|500 |
| |00516252 |9999/12/31|2013/05/06|500 |
| |00532215 |9999/12/31|2013/04/26|500 |
| |00590118 |9999/12/31|2013/04/26|500 |
| |00613479 |9999/12/31|2013/04/04|500 |
| |00622737 |9999/12/31|2013/04/08|500 |
| |00649295 |9999/12/31|2013/04/26|500 |
| |00661597 |2013/05/23|2013/04/26|500 |
| |00813966 |9999/12/31|2013/04/21|200 |
| |00871214 |9999/12/31|2013/04/12|500 |
----------------cut here------------------------------------

 

[Estuardo]

I tried your file and the vba code and worked for me.
the only significant difference is that i replace the acImportDelim with acImportFixed. so i ended up with this:

 Dim strFile As String
    strFile = "c:ToAccess.txt"
    DoCmd.TransferText acImportFixed, "NotepadCustomSpecs", "tblText", strFile

One suggestion:
Import you notepad data (again) with the wizard and just in the last step click on the advanced button --> The Import Specification window will pop up --> SaveAs (choose a new file name not the one you already have) -->ok-->finish

Try your vba code with the new specifications and the changed TypeTransfer.

G'd luck