Importing txt file

  • Thread starter Thread starter Igor G.
  • Start date Start date
I

Igor G.

I have problem with importing txt/csv files with 11 columns data inside.
When is data imported, there is just one column.
Here is my code:
--------------------
Dim strPath As String
Dim strFile As String

strPath = "C:\Tmp\"
strFile = Dir$(strPath & "Alarm.txt")

DoCmd.TransferText acImportDelim, Import, "Alarm", strPath & strFile,
False
 
Please show us an example of the data in the txt/csv file and also what you
want it to look like in the table.

Let's take a look at the acImportDelim argument. Are the fields in the table
delimited by commas, tabs, or something else?

If this is something that you need to do often, you may need to create an
import specification especially if there is a non-standard delimiter such as
the pipe | character. Do a manual import and look for an Advanced button in
the dialog boxes. Figure out things there and save the import spec.

By the looks of your code, Import is the name of your import spec. Is that
true? If so I believe that it needs to be in double quotes to work.
 
Here is example, three rows in 11 columns...
Thanks!

ALM 07:49:55 21PC1 PVHI 9.400 HIGH VRH V-4B
T2 9.402 21
ACK 07:50:40 21PC1 PVHI HIGH VRH V-4B
T2 1 31
RTN 07:50:56 21PC1 PVHI 9.400 HIGH VRH V-4B
T2 9.199 21
 
That data doesn't seem to be delimited. Try doing a manual import using fixed
width and see if that works. You may need to manually insert the divider
lines between the fields especially near the 1 towards the right side of the
record.
 
I was imported txis txt file manual without problems (just, next... next) but
I must create macro for automating this proces with external software.
Here is 11 regular columns. When i was pasted this sample look differently.
 
When you import the data manually, you're given a choice to save the import
specification. Once you've done that, you can specify the specification name
when you're invoking the TransferText method
 
Igor G. said:
Here is example, three rows in 11 columns...
Thanks!

ALM 07:49:55 21PC1 PVHI 9.400 HIGH VRH V-4B
T2 9.402 21
ACK 07:50:40 21PC1 PVHI HIGH VRH V-4B
T2 1 31
RTN 07:50:56 21PC1 PVHI 9.400 HIGH VRH V-4B
T2 9.199 21

"Jerry Whittle" wrote:

> Please show us an example of the data in the txt/csv file and also what you
> want it to look like in the table.
>
> Let's take a look at the acImportDelim argument. Are the fields in the table
> delimited by commas, tabs, or something else?
>
> If this is something that you need to do often, you may need to create an
> import specification especially if there is a non-standard delimiter such as
> the pipe | character. Do a manual import and look for an Advanced button in
> the dialog boxes. Figure out things there and save the import spec.
>
> By the looks of your code, Import is the name of your import spec. Is that
> true? If so I believe that it needs to be in double quotes to work.
> --
> Jerry Whittle, Microsoft Access MVP
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
>
> "Igor G." wrote:
>
> > I have problem with importing txt/csv files with 11 columns data inside.
> > When is data imported, there is just one column.
> > Here is my code:
> > --------------------
> > Dim strPath As String
> > Dim strFile As String
> >
> > strPath = "C:\Tmp\"
> > strFile = Dir$(strPath & "Alarm.txt")
> >
> > DoCmd.TransferText acImportDelim, Import, "Alarm", strPath & strFile,
> > False
> > --------------------
> >
> > How enter txt file to access table in 11 columns?
> > Thanks!
Click to expand...

Igor,
I think you're missing a data column in your second record.

You can import with space delimited, but in your data there appears to EndOfLine char after the 4B in your first row.

I would put your data into a text editor,
make sure all 11 fields are on one line,
make sure all fields are present,
each line terminated with EndOfLine char( 10-13) Cr Lf
Then import with space delimited.
 
Back
Top