Import semicolon delimited varied column file - need help

[matt -`;'-]

I have some text files of data in a comma delimited format that I would like to import into Access 2000, where the data is to be
imported in text format only - no numerical or date values format.

I am looking for a VBA method/code to accomplish this goal.

The import delimited text file format:
Title1;Title2;Title3;data1row1;data2row1;data3row1;data1row2;data2row2;data3row2

Is there a means to inform Access thru VBA that the delimiter is a semicolon, and is there a means to advise that all data is to be
imported as text values only? Not all of the files contain the same number of columns of data so I don't think a template can work.
The plan is to get the text files imported to tables without user intervention beyond clicking a button on a form.

I could do this the long way using my own code, but before I try that I was hoping there was a short way.

Thanks,
matt

 

[Ken Snell [MVP]]

Define an import specification where you declare that the semicolon is the
delimiter. Use that specification in a DoCmd.TransferText action in VBA
code.

TransferText is discussed quite well in Help files.

You define a specification by starting the import manually, setting all the
parameters, then on last window, click Advanced button at bottom left and
save the settings as a specification with a name that you choose. Then
cancel the import. Use that spec name in the TransferText method's
arguments.

 

[John Nurick]

If I understand you right, you want to get from this

Title1;Title2;Title3;data1row1;data2row1;data3row1;data1row2;data2row2;data3row2...

to this:

[Field names] Title1 Title2 Title3
[Record 1] data1row1 data2row1 data3row1
[Record 2] data1row2 data2row2 data3row2
[Record 3] ...

There's no way of doing this with Access's built-in text file routines,
but if you replace every third semicolon with a carriage return and line
field

If Perl is installed on your system you can just use this command at a
Windows prompt, substituting the name and location of your file:

C:\Bin>perl -0 -i.bak -pe"s/([^;]*);([^;]*);([^;]*)(?:;|$)/\1;\2;\3\n/g"
"C:\Folder\File.txt"

Otherwise it's possible to write VBA code to read the file, parse it
into records and fields, and append them to a table ... but it IMHO it's
a lot more work.

 

[matt -`;'-]

If I understand you right, you want to get from this

Title1;Title2;Title3;data1row1;data2row1;data3row1;data1row2;data2row2;data3row2...

to this:

[Field names] Title1 Title2 Title3
[Record 1] data1row1 data2row1 data3row1
[Record 2] data1row2 data2row2 data3row2
[Record 3] ...

There's no way of doing this with Access's built-in text file routines,
but if you replace every third semicolon with a carriage return and line
field

If Perl is installed on your system you can just use this command at a
Windows prompt, substituting the name and location of your file:

C:\Bin>perl -0 -i.bak -pe"s/([^;]*);([^;]*);([^;]*)(?:;|$)/\1;\2;\3\n/g"
"C:\Folder\File.txt"

Otherwise it's possible to write VBA code to read the file, parse it
into records and fields, and append them to a table ... but it IMHO it's
a lot more work.

I have some text files of data in a comma delimited format that I would like to import into Access 2000, where the data is to be
imported in text format only - no numerical or date values format.

I am looking for a VBA method/code to accomplish this goal.

The import delimited text file format:
Title1;Title2;Title3;data1row1;data2row1;data3row1;data1row2;data2row2;data3row2

Is there a means to inform Access thru VBA that the delimiter is a semicolon, and is there a means to advise that all data is to be
imported as text values only? Not all of the files contain the same number of columns of data so I don't think a template can work.
The plan is to get the text files imported to tables without user intervention beyond clicking a button on a form.

I could do this the long way using my own code, but before I try that I was hoping there was a short way.

Thanks,
matt

Thanks John!
I had a feeling that it was going to come down to me parsing it using my own code. I like the Perl idea, and I have it on my home
PC, but my workplace is a little wary of such things so I will have to take a VBA approach. I really wish I could have Perl at
work - its very cool.
I appreciate your help,
matt

 

[matt -`;'-]

Define an import specification where you declare that the semicolon is the
delimiter. Use that specification in a DoCmd.TransferText action in VBA
code.

TransferText is discussed quite well in Help files.

You define a specification by starting the import manually, setting all the
parameters, then on last window, click Advanced button at bottom left and
save the settings as a specification with a name that you choose. Then
cancel the import. Use that spec name in the TransferText method's
arguments.

Hi Ken,
I thought the import specifier works when you have it set to a certain specific number of columns. My problem is that one file may
have 5 columns and another may have 8. If the specifier is set to only one of those files then it would not work on the other
because of the difference in the number of columns? Is it possible to have a specifier for 8 columns and use that same specifier on
the 5 column file? I probably have to match the column counts to the specifier don't I?
Thanks,
matt

 

[Ken Snell [MVP]]

Yes, the specification will "fix" the number of columns that are being
imported, so variable number of columns will be a problem. Sorry about that.

 

[John Nurick]

In VBA:

Sub InsertLineBreakEveryThirdDelimiter()
Dim InFile As Long
Dim OutFile As Long
Dim C As String * 1
Dim CountDelimiters As Long

InFile = FreeFile()
Open "C:\Temp\NoRS.txt" For Input As InFile
OutFile = FreeFile()
Open "C:\Temp\Fixed.txt" For Output As OutFile

Do Until EOF(InFile)
C = Input(1, #InFile)
If C = ";" Then
CountDelimiters = CountDelimiters + 1
If CountDelimiters = 3 Then
CountDelimiters = 0
Print #OutFile, vbCrLf;
Else
Print #OutFile, C;
End If
Else
Print #OutFile, C;
End If
Loop

Close #InFile
Close #OutFile
End Sub

 

[matt -`;'-]

Yes, the specification will "fix" the number of columns that are being
imported, so variable number of columns will be a problem. Sorry about that.

That's okay - I was hoping for an easy way. I did write some VBA code (which I like doing anyway) that allows me to import a
semicolon delimited file directly into an Access table where all the data is imported as text values. All by a click of a button.
It took a little effort, but I am very happy with the results. This is going to save me considerable time at work since I have
certain files that I need to import as all text so that I can evaluate the data before converting it over to chosen data types.
Access with VBA is awesome!
Thank you for taking the time, matt