×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Reading Tab Delimited File Using ADO

Reading Tab Delimited File Using ADO

Reading Tab Delimited File Using ADO

(OP)
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.

RE: Reading Tab Delimited File Using ADO

Any code we can look at?

RE: Reading Tab Delimited File Using ADO

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/sql/odbc/microsof...

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Reading Tab Delimited File Using ADO

(OP)
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

RE: Reading Tab Delimited File Using ADO

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Reading Tab Delimited File Using ADO

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

RE: Reading Tab Delimited File Using ADO

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

Swi

RE: Reading Tab Delimited File Using ADO

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Reading Tab Delimited File Using ADO

(OP)
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

RE: Reading Tab Delimited File Using ADO

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Reading Tab Delimited File Using ADO

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.

RE: Reading Tab Delimited File Using ADO

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

Swi

RE: Reading Tab Delimited File Using ADO

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.

RE: Reading Tab Delimited File Using ADO

(OP)
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

RE: Reading Tab Delimited File Using ADO

(OP)
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

RE: Reading Tab Delimited File Using ADO

(OP)
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

RE: Reading Tab Delimited File Using ADO

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 ...

RE: Reading Tab Delimited File Using ADO

(OP)
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

RE: Reading Tab Delimited File Using ADO

(OP)
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

RE: Reading Tab Delimited File Using ADO

This uses an similar approach to retaining whitespace

CODE

' 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
Public Sub test()
    Dim m As String
    Dim fso As New FileSystemObject
    Dim header As String
    Dim myfile As TextStream
    Dim lp As Long

    ' Grab source file text
    With fso.OpenTextFile("F:\Michael\Documents\deletemesrc.txt")
        header = .ReadLine
        m = .ReadAll
    End With
    
    ' Delimit
    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
    
    ' Save delimited version
    With fso.OpenTextFile("F:\Michael\Documents\deleteme.txt", ForWriting)
        .WriteLine header
        .Write m
    End With

    With CurrentDb
        ' Import delimited text file. Example assumes existence of relevant schema.ini file
        .Execute "SELECT * INTO deleteme FROM [Text;FMT=Delimited;HDR=Yes;DATABASE=F:\Michael\Documents;].[deleteme.txt];", False
           
        ' Clean off text delimiters (chr$(34) for purposes of this example)
        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 ' Access ...
End Sub 

RE: Reading Tab Delimited File Using ADO

(OP)
Thanks!

Swi

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close