Reading Tab Delimited File Using ADO 1

Swi · Jun 4, 2018

I am reading a tab delimited text file using ADO. However, it seems to be removing any leading and trailing spaces on my fields. Is there any way around this? Thanks.

strongm · Jun 7, 2018

Any code we can look at?

SkipVought · Jun 7, 2018

This comes from the way-back machine. I seem to recall using ADO or Jet to read text files, and I had to set up a file to define the file structure of the text file I wanted to read via ADO or Jet. I remember it being referred to an a “.ini” file. This .ini file eventually contained many file definitions. I believe that the actual file name was schema.ini, a configuration file.

Did some digging. This may get you off in the general direction:

https://docs.microsoft.com/en-us/sq...ni-file-text-file-driver?view=sql-server-2017

And can you supply an example of the data in the tab delimited file, having leading or trailing spaces?

And what database are you using ADO to get this tab delimited data into?

Skip,
[sub]
[glasses]

Just traded in my OLD subtlety...
for a NUance! [tongue]

[/sub]

Swi · Jun 11, 2018

Yes. Already using an ini file. Some fields are huge so storing all as memo but even if storing as another type of text it still strips off any trailing or leading space in the fields.

Code:

            TextFileName = objFile.Name
            Set Instream = fso.OpenTextFile(MinFilePath & TextFileName, ForReading)
            aryHeaders = Split(Instream.ReadLine, vbTab)
            Instream.Close
            
            Set OutStream = fso.OpenTextFile(MinFilePath & "\schema.ini", ForWriting, True)
            OutStream.WriteLine "[" & TextFileName & "]"
            OutStream.WriteLine "ColNameHeader = True"
            OutStream.WriteLine "Format = TabDelimited"
            OutStream.WriteLine "MaxScanRows = 0"
            For i = 0 To UBound(aryHeaders)
                OutStream.WriteLine "Col" & i + 1 & "=" & aryHeaders(i) & " Memo"
            Next
            OutStream.Close

            CSVSql = ""
            For i = 0 To lstFields.ListCount - 1
                CSVSql = CSVSql & "[" & lstFields.List(i) & "],"
            Next
            CSVSql = Left(CSVSql, (Len(CSVSql) - 1))
    
            rstxt.Open "SELECT * INTO [Master] IN '" & App.Path & "\" & MDBFileName & "' FROM [" & TextFileName & "]", conn, adOpenStatic, adLockReadOnly, adCmdText
            
            If rstxt.State = adStateOpen Then rstxt.Close

Swi

SkipVought · Jun 11, 2018

Well the .ini question was answered, but...

Skip,
[sub]
[glasses]

Just traded in my OLD subtlety...
for a NUance! [tongue]

[/sub]

strongm · Jun 11, 2018

Ok, well ADO will strip whitespace from strings in tab delimited files that do not have a text qualifier around them (eg " or ') You also have to tell it, in schema.ini to drop the text qualifier, if you don't want it included in the import, so add eg

TextDelimiter="

to schema.ini

Swi · Jun 11, 2018

Ok, that is not good as the clients data does not have a " or ' as a text qualifier. Thanks.

Swi

SkipVought · Jun 11, 2018

Under what circumstances does your text data have signicant leading or trailing spaces?

Skip,
[sub]
[glasses]

Just traded in my OLD subtlety...
for a NUance! [tongue]

[/sub]

Swi · Jun 12, 2018

Mostly trailing spaces. We receive data from car dealerships and they have coupon fields that are 230 bytes max. These can cut off in the middle of a word which is not an issue or when there is a space which is where the issue comes in. Thanks.

Swi

SkipVought · Jun 12, 2018

So it’s not so much that there are trailing spaces as much as reaching a limit of characters in a cell?

Skip,
[sub]
[glasses]

Just traded in my OLD subtlety...
for a NUance! [tongue]

[/sub]

MakeItSo · Jun 13, 2018

Can you use LibreOffice as a workaround or to reformat the file?
I just tried opening a tab text in Calc with leading and trailing spaces in values without text qualifier - worked fine!
If that is an option, maybe you could save in a different format from there - or as tab text with qualifiers.

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

Swi · Jun 13, 2018

Thanks for the tip MakeItSo. Unfortunately there are hundreds of files.

Swi

MakeItSo · Jun 13, 2018

Then use fso to read the text file line by line and write to a new one.

Write first line drectly as is (headers).
Write all following lines as:

Code:

chr(34) + Replace(line, chr(9), chr(34)+chr(9)+chr(34)) + chr(34)

This should give you a text file with all content enclosed in quotes.

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

Swi · Jun 13, 2018

Ok, tried that method, got the files all tab/quote delimited and still not luck. It is stripping the trailing spaces. Here is an example of the ini file.

Code:

[CR3_17_AL1994_7bb79cd4-f3a3-4853-9572-cad1e4a7828b.csv]
ColNameHeader = True
Format = TabDelimited
TextDelimiter = "
MaxScanRows = 0
Col1="BatchID" Memo
Col2="BatchType" Memo
Col3="NotificationID" Memo
Col4="ExternalCompanyID" Memo
Col5="LoopCompanyId" Memo
Col6="DocumentID" Memo
Col7="DMSCustomerID" Memo
Col8="RequestedShipDate" Memo
Col9="FirstName" Memo
Col10="MiddleName" Memo
Col11="LastName" Memo
Col12="Suffix" Memo
Col13="Address1" Memo
Col14="Address2" Memo
Col15="City" Memo
Col16="State" Memo
Col17="Zip" Memo
Col18="VehicleMake" Memo
Col19="VehicleModel" Memo
Col20="VehicleYear" Memo
Col21="VIN" Memo
Col22="VehicleLastServiceDate" Memo
Col23="DealerName" Memo
Col24="DealerAddress" Memo
Col25="DealerCity" Memo
Col26="DealerState" Memo
Col27="DealerZip" Memo
Col28="DealerPhone" Memo
Col29="DealerContactName" Memo
Col30="DealerContactTitle" Memo
Col31="DealerContactPhone" Memo
Col32="DealerContactEmail" Memo
Col33="DealerHours" Memo
Col34="DealerURL" Memo
Col35="WillSendEmail" Memo
Col36="LoopServiceId" Memo
Col37="Level" Memo
Col38="Months" Memo
Col39="Category" Memo
Col40="Urgency" Memo
Col41="SIFriendlyName" Memo
Col42="IsVehicleMake" Memo
Col43="IsLoyalty" Memo
Col44="IsVehicleMileageGroup" Memo
Col45="AnniversaryYears" Memo
Col46="USP1" Memo
Col47="USP2" Memo
Col48="USP3" Memo
Col49="USP4" Memo
Col50="USP5" Memo
Col51="USP6" Memo
Col52="USP7" Memo
Col53="USP8" Memo
Col54="coupon1_Title" Memo
Col55="coupon1_Amount" Memo
Col56="coupon1_Subtitle" Memo
Col57="coupon1_Body" Memo
Col58="coupon1_BodyB" Memo
Col59="coupon1_Disclaimer" Memo
Col60="coupon1_Expiration_Date" Memo
Col61="coupon2_Title" Memo
Col62="coupon2_Amount" Memo
Col63="coupon2_Subtitle" Memo
Col64="coupon2_Body" Memo
Col65="coupon2_BodyB" Memo
Col66="coupon2_Disclaimer" Memo
Col67="coupon2_Expiration_Date" Memo
Col68="FirstClassUpgrade" Memo
Col69="PrintSamples" Memo
Col70="CustomerPin" Memo

Swi

Swi · Jun 13, 2018

So, if I do this:

TextDelimiter = none

I get the trailing spaces but with quotes slugged into the database as strongm said. However when I leave it out or put TextDelimiter = " it just strips the spaces anyway.

Thanks.

Swi

Swi · Jun 13, 2018

SkipVought,

Sorry I missed your question. No, I am storing all as memo so no big deal there. They just are breaking their fields in different size chunks and therefore it could be in the middle of a sentence or middle of a word.

Thanks.

Swi

strongm · Jun 13, 2018

I'd forgotten that Access will strip trailing spaces on importing text fields, whatever you do... it's only leading whitespace that my suggestion rescues ...

Swi · Jun 13, 2018

Ah, ok, thanks. I *think* I have a workaround. Not sure if this is the best way but..... Not pretty but seems to be working.

First I read the files and make them tab/quote delimited.

Code:

            Set Instream = fso.OpenTextFile(FilePath & TextFileName, ForReading)
            Set OutStream = fso.OpenTextFile(FilePath & "minified\" & TextFileName, ForWriting, True)
            Counter = 0
            Do
                aryHeaders = Split(Instream.ReadLine, vbTab)
                InputData = ""
                For i = 0 To lstHeaders.ListCount - 1
                    Counter = Counter + 1
                    If Len(aryHeaders(lstHeaders.List(i))) > 2 Then
                        InputData = InputData & Chr(34) & Replace(Left(aryHeaders(lstHeaders.List(i)), 1), Chr(34), "") & _
                        Mid(aryHeaders(lstHeaders.List(i)), 2, Len(aryHeaders(lstHeaders.List(i))) - 2) & _
                        Replace(Right(aryHeaders(lstHeaders.List(i)), 1), Chr(34), "") & Chr(34) & vbTab
                    ElseIf aryHeaders(lstHeaders.List(i)) = String(2, Chr(34)) Then
                        InputData = InputData & String(2, Chr(34)) & vbTab
                    Else
                        InputData = InputData & Chr(34) & aryHeaders(lstHeaders.List(i)) & Chr(34) & vbTab
                    End If
                Next
                OutStream.WriteLine Left(InputData, (Len(InputData) - 1))
            Loop Until Instream.AtEndOfStream
            Instream.Close
            OutStream.Close

Then I slug them into database with the quotes included (Textdelimiter = none).

Code:

            Set OutStream = fso.OpenTextFile(MinFilePath & "\schema.ini", ForWriting, True)
            OutStream.WriteLine "[" & TextFileName & "]"
            OutStream.WriteLine "ColNameHeader = True"
            OutStream.WriteLine "Format = TabDelimited"
            OutStream.WriteLine "TextDelimiter = none"
            OutStream.WriteLine "MaxScanRows = 0"
            For i = 0 To UBound(aryHeaders)
                OutStream.WriteLine "Col" & i + 1 & "=" & aryHeaders(i) & " Memo"
            Next
            OutStream.Close

            CSVSql = ""
            For i = 0 To lstFields.ListCount - 1
                CSVSql = CSVSql & "[" & lstFields.List(i) & "],"
            Next
            CSVSql = Left(CSVSql, (Len(CSVSql) - 1))
    
            rstxt.Open "SELECT * INTO [Master] IN '" & App.Path & "\" & MDBFileName & "' FROM [" & TextFileName & "]", conn, adOpenStatic, adLockReadOnly, adCmdText

            If rstxt.State = adStateOpen Then rstxt.Close
            conn.Close
            Set rstxt = Nothing
            If fso.FileExists(MinFilePath & "\schema.ini") Then fso.DeleteFile MinFilePath & "\schema.ini"
            
            conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & App.Path & "\" & MDBFileName & "'"
            conn.Execute "INSERT INTO [Final] (" & CSVSql & ") SELECT " & CSVSql & " FROM [Master]"

I can manipulate in here and do whatever I need. Then I can write all resultant files out of my main table using ADO to a CSV which is the end product.

Code:

        Set rstxt = New ADODB.Recordset
        rstxt.Open "SELECT * FROM [Final]", conn
        Set OutStream = fso.OpenTextFile(FilePath & "Test_" & Format(Date, "MM-DD-YYYY") & ".txt", ForWriting, True)
        HeaderTxtFnl = ""
        For z = 0 To rstxt.Fields.Count - 1
            HeaderTxtFnl = HeaderTxtFnl & Chr(34) & rstxt.Fields(z).Name & Chr(34) & ","
        Next
        OutStream.WriteLine Left(HeaderTxtFnl, Len(HeaderTxtFnl) - 1)
        OutStream.Write rstxt.GetString(adClipString, , ",", vbCrLf, "")
        OutStream.Close
        rstxt.Close
        conn.Close
        Set conn = Nothing
        Set rstxt = Nothing
        Set OutStream = Nothing

Swi

Swi · Jun 13, 2018

BTW, the reason for the below code is due to the company that is supplying the files sending me some files that are tab/quote delimited and some not. This way if anything is over 2 characters I check to see if it is already quote delimited.

Code:

                    If Len(aryHeaders(lstHeaders.List(i))) > 2 Then ' Check for populated strings and any enclosed quotes
                        InputData = InputData & Chr(34) & Replace(Left(aryHeaders(lstHeaders.List(i)), 1), Chr(34), "") & _
                        Mid(aryHeaders(lstHeaders.List(i)), 2, Len(aryHeaders(lstHeaders.List(i))) - 2) & _
                        Replace(Right(aryHeaders(lstHeaders.List(i)), 1), Chr(34), "") & Chr(34) & vbTab
                    ElseIf aryHeaders(lstHeaders.List(i)) = String(2, Chr(34)) Then ' Check for blank strings with enclosed quotes
                        InputData = InputData & String(2, Chr(34)) & vbTab
                    Else ' Write out any other string adding enclosed quotes
                        InputData = InputData & Chr(34) & aryHeaders(lstHeaders.List(i)) & Chr(34) & vbTab
                    End If

Swi

strongm · Jun 14, 2018

This uses an similar approach to retaining whitespace

Code:

[blue][green]' Note example is using DAO in Access (as that si what I had at hand), but should need very little work to turn into ADO
' Requires references to Microsoft VBScript Regular Expressions 5.5 and Microsoft Scripting Runtime[/green]
Public Sub test()
    Dim m As String
    Dim fso As New FileSystemObject
    Dim header As String
    Dim myfile As TextStream
    Dim lp As Long

    [green]' Grab source file text[/green]
    With fso.OpenTextFile("F:\Michael\Documents\deletemesrc.txt")
        header = .ReadLine
        m = .ReadAll
    End With
    
    [green]' Delimit[/green]
    With New RegExp
        .Global = True
        .Multiline = True
        .Pattern = "(\t)"
        m = .Replace(m, Chr$(34) & "$1" & Chr$(34))
        .Pattern = "^(.+?)($)"
        m = .Replace(m, Chr$(34) & "$1" & Chr$(34))
    End With
    
    [green]' Save delimited version[/green]
    With fso.OpenTextFile("F:\Michael\Documents\deleteme.txt", ForWriting)
        .WriteLine header
        .Write m
    End With

    With CurrentDb
        [green]' Import delimited text file. Example assumes existence of relevant schema.ini file[/green]
        .Execute "SELECT * INTO deleteme FROM [Text;FMT=Delimited;HDR=Yes;DATABASE=F:\Michael\Documents;].[deleteme.txt];", False
           
        [green]' Clean off text delimiters (chr$(34) for purposes of this example)[/green]
        For lp = 0 To .TableDefs("DELETEME").fields.Count - 1
            With .TableDefs("DELETEME").fields(lp)
                CurrentDb.Execute "UPDATE deleteme SET [" & .Name & "] = REPLACE([" & .Name & "], chr$(34), '')"
            End With
        Next
        
         .TableDefs.Refresh
    End With
    Application.RefreshDatabaseWindow [green]' Access ...[/green]
End Sub[/blue]

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Reading Tab Delimited File Using ADO 1

Programmer

MIS

Programmer

Programmer

Programmer

MIS

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

MIS

Programmer

Programmer

MIS

Similar threads

Log in

Part and Inventory Search

Sponsor