Delimited text File Import


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 13 of 13

Thread: Delimited text File Import

  1. #1
    Join Date
    Oct 2005
    Posts
    133

    Delimited text File Import

    Hi All

    i have a major problem and cant seem to sort it out

    i downloaded a compiled Activex control that generates an entire cd's file properties into a text file delimited by commas
    i have searched all over this PSC and Devx and found many access import tools but none of them seem to actually import into access they all import into vb6 visual data manager so my question is there a way to import a vb6 vdm file into access without having to convert it when opening it in access..
    please help

    my text file is as follows
    d:\CCS,0,03/09/2005 12:27:07,R=True,A=False,S=False,H=False
    d:\CLEANUP2,0,03/09/2005 12:25:59,R=True,A=False,S=False,H=False
    d:\ENGINE,0,03/09/2005 12:24:08,R=True,A=False,S=False,H=False
    d:\ESS,0,03/09/2005 12:27:12,R=True,A=False,S=False,H=False
    d:\EZSTutor,0,03/09/2005 12:27:31,R=True,A=False,S=False,H=False
    d:\Graphics,0,03/09/2005 12:27:39,R=True,A=False,S=False,H=False
    d:\KDEVICES,0,03/09/2005 12:23:14,R=True,A=False,S=False,H=False
    d:\Ksu,0,03/09/2005 12:26:37,R=True,A=False,S=False,H=False
    d:\Notifier,0,03/09/2005 12:27:46,R=True,A=False,S=False,H=False
    d:\OTTBP,0,03/09/2005 12:25:21,R=True,A=False,S=False,H=False

    please could anyone help me to get this to work or point me in the right direction of converting vsd files into access format within vb6
    many thanks in advance

    Rob

  2. #2
    Join Date
    Aug 2005
    Posts
    51
    From Acces Help file:

    Important Before you import or link data from a delimited text file or fixed-width text file, make sure that the file has the same type of data in each field and the same fields in every row.

    Open a database, or switch to the Database window for the open database.


    To import data, on the File menu, point to Get External Data, and then click Import.
    To link data, on the File menu, point to Get External Data, and then click Link Tables.

    In the Import (or Link) dialog box, in the Files Of Type box, select Text Files (*.txt;*.csv;*.tab;*.asc).


    Click the arrow to the right of the Look In box, select the drive and folder where the file is located, and then double-click its icon.
    Important If you link to a file on a local area network, make sure that you use a universal naming convention (UNC) path, instead of relying on the drive letter of a mapped network drive in Windows Explorer. A drive letter can vary on a computer, or it may not always be defined; whereas, a UNC path is a reliable and consistent way for Microsoft Access to locate the data source that contains the linked table.

    Follow the directions in the Import Text Wizard dialog boxes. Click Advanced to create or use an import/export specification.
    Notes

    Although you usually create a new table in Microsoft Access for the data, you can append the data to an existing table as long as the first row of your text file contains matching field names.


    If importing a text file takes an unexpectedly long time, it might be because many errors are occurring. To cancel importing, press CTRL+BREAK.


    In a fixed-width text file, you can ignore fields at the end of a record that contain no data. In addition, the last field with data in the record can be less than the maximum width.


    If all the records in a fixed-width text file are the same length, there can be an embedded row separator (such as a carriage return and linefeed) in the middle of a record. If the records aren't all the same length, embedded row separators should not be used because Microsoft Access will treat the embedded row separator as the end of the record.

  3. #3
    Join Date
    Oct 2005
    Posts
    133

    thanks

    thanks mate

    but i was hoping that i could get help freom within vb6 using vb6 code

    many thanks

  4. #4
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  5. #5
    Join Date
    Oct 2005
    Posts
    133
    hi Phil,

    thanks for your help there but was wandering if you could lend me another one of your brain cells please
    i copy and pasted the code you showed me, changed the names accordingly to my table and database and also text file name

    and i see it says at the top of the code to construct the sql..

    so i inserted my field names but i dont know what the values are ..

    maybe if i post my code so far then it would possibly explain better than me

    CODE

    Public Function ImportTextFile(cn As Object, _
    ByVal tblName As String, FileFullPath As String, _
    Optional FieldDelimiter As String = ",", _
    Optional RecordDelimiter As String = vbCrLf) As Boolean

    Dim cmd As New adodb.Command
    Dim rs As New adodb.Recordset
    Dim sFileContents As String
    Dim iFileNum As Integer
    Dim sTableSplit() As String
    Dim sRecordSplit() As String
    Dim lCtr As Integer
    Dim iCtr As Integer
    Dim iFieldCtr As Integer
    Dim lRecordCount As Long
    Dim iFieldsToImport As Integer

    'These variables prevent having to requery a recordset for each record
    Dim asFieldNames() As String
    Dim abFieldIsString() As Boolean
    Dim iFieldCount As Integer
    Dim sSQL As String
    Dim bQuote As Boolean

    'On Error GoTo errHandler
    If Not TypeOf cn Is adodb.Connection Then Exit Function
    If Dir(FileFullPath) = "" Then Exit Function

    If cn.State = 0 Then cn.Open
    Set cmd.ActiveConnection = cn
    cmd.CommandText = tblName
    cmd.CommandType = adCmdTable
    Set rs = cmd.Execute
    iFieldCount = rs.Fields.Count
    rs.Close

    ReDim asFieldNames(iFieldCount - 1) As String
    ReDim abFieldIsString(iFieldCount - 1) As Boolean

    For iCtr = 0 To iFieldCount - 1
    asFieldNames(iCtr) = "[" & rs.Fields(iCtr).Name & "]"
    abFieldIsString(iCtr) = FieldIsString(rs.Fields(iCtr))
    Next


    iFileNum = FreeFile
    Open FileFullPath For Input As #iFileNum
    sFileContents = Input(LOF(iFileNum), #iFileNum)
    Close #iFileNum
    'split file contents into rows
    sTableSplit = Split(sFileContents, RecordDelimiter)
    lRecordCount = UBound(sTableSplit)
    'make it "all or nothing: whole text
    'file or none of it
    cn.BeginTrans

    For lCtr = 0 To lRecordCount - 1
    'split record into field values

    sRecordSplit = Split(sTableSplit(lCtr), FieldDelimiter)
    iFieldsToImport = IIf(UBound(sRecordSplit) + 1 < _
    iFieldCount, UBound(sRecordSplit) + 1, iFieldCount)

    'construct sql
    sSQL = "INSERT INTO " & tblName & " ([Path],[Size],[Created],[R],[S],[A],[H]) "

    For iCtr = 0 To iFieldsToImport - 1
    bQuote = abFieldIsString(iCtr)
    sSQL = sSQL & asFieldNames(iCtr)
    If iCtr < iFieldsToImport - 1 Then sSQL = sSQL & ","
    Next iCtr

    sSQL = sSQL & ") VALUES ("

    For iCtr = 0 To iFieldsToImport - 1
    If abFieldIsString(iCtr) Then
    sSQL = sSQL & prepStringForSQL(sRecordSplit(iCtr))
    Else
    sSQL = sSQL & sRecordSplit(iCtr)
    End If

    If iCtr < iFieldsToImport - 1 Then sSQL = sSQL & ","
    Next iCtr

    sSQL = sSQL & ")"
    cn.Execute sSQL

    Next lCtr

    cn.CommitTrans
    rs.Close
    Close #iFileNum
    Set rs = Nothing
    Set cmd = Nothing

    ImportTextFile = True
    Exit Function

    errHandler:
    On Error Resume Next
    If cn.State <> 0 Then cn.RollbackTrans
    If iFileNum > 0 Then Close #iFileNum
    If rs.State <> 0 Then rs.Close
    Set rs = Nothing
    Set cmd = Nothing


    End Function

    Private Function FieldIsString(FieldObject As adodb.Field) _
    As Boolean

    Select Case FieldObject.Type
    Case adBSTR, adChar, adVarChar, adWChar, adVarWChar, _
    adLongVarChar, adLongVarWChar
    FieldIsString = True
    Case Else
    FieldIsString = False
    End Select

    End Function

    Private Function prepStringForSQL(ByVal sValue As String) _
    As String

    Dim sAns As String
    sAns = replace(sValue, Chr(39), "''")
    sAns = "'" & sAns & "'"
    prepStringForSQL = sAns

    End Function

    Private Sub Command3_Click()
    Dim cn As New adodb.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\pcc\data\db1.mdb"
    cn.Open
    ImportTextFile cn, "table1", "C:\pcc\output\kodak.txt"
    cmd.Execute

    End Sub

    END CODE

    Please help cause i am getting a Syntax error in my "Insert into Statement"
    as i cant give the value for my fields cause i am not to sure on how to approach that

    Many Thanks in advance

  6. #6
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    Did you try running the code? It should work fine now. The SQL values are generated from the text file by this loop:
    Code:
        For iCtr = 0 To iFieldsToImport - 1
            If abFieldIsString(iCtr) Then
                 sSQL = sSQL & prepStringForSQL(sRecordSplit(iCtr))
            Else
                sSQL = sSQL & sRecordSplit(iCtr)
            End If
            
            If iCtr < iFieldsToImport - 1 Then sSQL = sSQL & ","
        Next iCtr
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  7. #7
    Join Date
    Oct 2005
    Posts
    133

    again

    hi again lol

    yes i have actually run the code but yet still i get the same problem

    a syntax error in my insert into statement


    and then highlights the following

    cn.execute sSql


    and now i am out of ideas since this import is something i have never done beofer

    please help again lol

    thanks in advance and thanks for the help as of yet

  8. #8
    Join Date
    Aug 2005
    Posts
    51
    Sounds like your SQL statement is getting hosed. Try doing this:

    everytime your code has a sSQL = , follow that line with a Debug.Print sSQL to see your exact SQL statement as it's being constructed. Chances are it's syntax and you are missing a ( or) or ' mark around a value, or you have an extra one of these.

    Looks you might have an extra ( here:
    Code:
    'construct sql
    sSQL = "INSERT INTO " & tblName & " ([Path],[Size],[Created],[R],[S],[A],[H])  "
    
    For iCtr = 0 To iFieldsToImport - 1
    bQuote = abFieldIsString(iCtr)
    sSQL = sSQL & asFieldNames(iCtr)
    If iCtr < iFieldsToImport - 1 Then sSQL = sSQL & ","
    Next iCtr
    
    sSQL = sSQL & ")  VALUES ("

    Also, if you already provide the field names with:
    Code:
    sSQL = "INSERT INTO " & tblName & " ([Path],[Size],[Created],[R],[S],[A],[H]) "
    Why do you provide them again with:
    Code:
    For iCtr = 0 To iFieldsToImport - 1
    bQuote = abFieldIsString(iCtr)
    sSQL = sSQL & asFieldNames(iCtr)
    If iCtr < iFieldsToImport - 1 Then sSQL = sSQL & ","
    Next iCtr
    
    sSQL = sSQL & ") VALUES ("

  9. #9
    Join Date
    Oct 2005
    Posts
    133

    ermmm

    this is now my insert statement
    sSQL = "INSERT INTO " & tblName & " ("

    and this is my execute command button

    Private Sub Command3_Click()
    Dim cn As New adodb.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\pcc\data\db1.mdb"
    cn.Open
    ImportTextFile cn, "table1", "C:\pcc\output\kodak easyshare.txt"
    cmd.Execute

    End Sub

    but it still tells me there is an syntax error in my insert statement no matter what i do too it

    i have tried all i know about ado and nothing is right

    anymore suggestions please

    thanks

  10. #10
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    We need to know the value of the sSQL variable when the error occurs. Add this line to ImportTextFile just before "cn.Execute sSQL":

    Debug.Print sSQL

    Then set a breakpoint on "cn.Execute sSQL". Copy and paste the INSERT statement that appears in the debug window into a reply to this message.
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  11. #11
    Join Date
    Oct 2005
    Posts
    133
    INSERT INTO table1 ([Path],[Size],[Created],[r],[s],[a],[h]) VALUES ('d:\OTTBP\icm54.cab','413625','04/08/2005 08:20:20','R=True','A=False','S=False','H=False')
    INSERT INTO table1 ([Path],[Size],[Created],[r],[s],[a],[h]) VALUES ('d:\OTTBP\icm55.cab','435265','04/08/2005 08:20:39','R=True','A=False','S=False','H=False')
    INSERT INTO table1 () VALUES ()

    that was written in the immediate window at the bottom i can see one error just looking at it i assume that the fact that it is placing the ',' between each value but to my knowledge it is supposed to be placing " ',' " meanint that it is just missing the inverted comma marks
    but i dont know where to insert them into

    thanks

    i can now also say that it goes mental with my insert into statement when there are no more entrie to import

    thankx again

  12. #12
    Join Date
    Oct 2005
    Posts
    133
    can anyone help with this above issue

    many thanks

  13. #13
    Join Date
    Oct 2005
    Posts
    133

    yay thanks everyone

    yay got it working after all this time ... my thanks and gratitude to you all for your assistance... i have but one question left

    now that all the entries within my text file are being imported successfully is it possible to add a constant field to the import section , such as since this is a directory scan of a cd can i possible generate a four constant field with the cd name , i have the code to retrieve the cd volume information into a text box (Text3) but am wondering how and where to insert into my code to get it to add a new field called CDTIT and enter a constant for each entry in my text file as text3 ..

    please help

    many thanks in advance
    you guys are great

Similar Threads

  1. Sending mail with image problem
    By vikassheelgupta in forum Java
    Replies: 0
    Last Post: 01-06-2006, 01:12 AM
  2. Replies: 3
    Last Post: 08-23-2001, 11:01 AM
  3. Replies: 2
    Last Post: 02-09-2001, 03:53 PM
  4. Replies: 0
    Last Post: 06-01-2000, 03:57 AM
  5. Replies: 0
    Last Post: 04-17-2000, 01:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center