VBA Import CSV Into Table

[jo15765]

I am importing a csv into an access table. In Excel Column D, the value could be 111111 or could be 111111-000, if the value is hyphenated, I want to split on the hyphen and everything to the left of the hyphen goes in table field name GymIdNum, and everything to the right of the hyphen goes in GymIdVar, then Column E would be the next field name of the database table

For i = 2 To xlWorksheet.UsedRange.Rows.Count
    row = xlWorksheet.Rows(i).Value
    rs.AddNew
    For j = 1 To 17
        If j = 4 And InStr(1, row(1, j), "-") > 0 Then
            Dim parts() As String
            parts = Split(row(1, j), "-")
            rs.Fields(j - 1).Value = parts(0)
            rs.Fields(j).Value = parts(1)
            j = j + 1
        ElseIf j < 4 Then
            rs.Fields(j - 1).Value = row(1, j)
        Else
            rs.Fields(j).Value = CStr(row(1, j - 1))
        End If
    Next j
    rs.Update
Next i

[June7]

So, what is the issue? Does code work? Error message, wrong result, nothing happens?

[ranman256]

normally you dont import text files 1 record at a time


'*******************
' TEXT FILE IMPORT
'*******************

attach the text file as an external table,
then make an append query(s) to import the columns and data you wish.
the query will correct errors in the data


text files require a SPEC file to define the columns so there's some pre-steps to take:

---------------
PREP
---------------
1st, manually import the text file,
in the 'import screen' ,bottom left corner is ADVANCED
define your fields in the text as an IMPORT SPEC.
SAVE this spec. (i.e: "SpecName")


manually link this CSV file using the spec name so it becomes an External Table: txtFile2Import

make an append query to append the txtFile2Import fields to the target table: qaImportCsvFile


---------------
EXECUTION
---------------
1. each new CSV file you get, save it to the same place every time,like: c:\temp\txtFile2Import.csv
2. run the import query(s): docmd.openquery "qaImportCsvFile"
3. Done

[jo15765]

No it is not working for me....what I am doing is first creating the table in vba

Sub CreateStagingTable()
    Dim db As Database
    Dim tbl As TableDef
    Dim fld As Field
    Set db = CurrentDb
    
    Set tbl = db.CreateTableDef("ABCD_Temp")
    tbl.Fields.Append tbl.CreateField("Location", dbText, 255)
    tbl.Fields.Append tbl.CreateField("CreatedBy", dbText, 255)
    tbl.Fields.Append tbl.CreateField("ResNum", dbText, 255)
    tbl.Fields.Append tbl.CreateField("CNum", dbText, 255)
    tbl.Fields.Append tbl.CreateField("VNum", dbText, 255)
    tbl.Fields.Append tbl.CreateField("SNum", dbText, 255)
    tbl.Fields.Append tbl.CreateField("ST", dbText, 255)
    tbl.Fields.Append tbl.CreateField("AN", dbText, 255)
    tbl.Fields.Append tbl.CreateField("Name", dbText, 255)
    tbl.Fields.Append tbl.CreateField("Address", dbText, 255)
    tbl.Fields.Append tbl.CreateField("City", dbText, 255)
    tbl.Fields.Append tbl.CreateField("St", dbText, 255)
    tbl.Fields.Append tbl.CreateField("Zip", dbText, 255)
    tbl.Fields.Append tbl.CreateField("Phone", dbText, 255)
    tbl.Fields.Append tbl.CreateField("RDA", dbCurrency)
    tbl.Fields.Append tbl.CreateField("SDA", dbCurrency)
    tbl.Fields.Append tbl.CreateField("TID", dbCurrency)
    tbl.Fields.Append tbl.CreateField("Green", dbText, 255)
    tbl.Fields.Append tbl.CreateField("CD", dbText, 255)
    
    db.TableDefs.Append tbl
    db.Close
    Set fld = Nothing
    Set tbl = Nothing
    Set db = Nothing
    
    MsgBox "Table created successfully.", vbInformation
End Sub

Then trying to import the file with vba....it was working until the requirement to split on the hyphen into two diff columns

Private Sub ImportSelectedFile(filePath)
Dim db As Database
Dim tbl As TableDef
Dim rs As Recordset
Dim row As Variant
Dim i As Integer
Dim j As Integer


Set db = CurrentDb
Set tbl = db.TableDefs("ABCD_Temp")
Set rs = tbl.OpenRecordset


Dim xlApp As Object
Dim xlWorkbook As Object
Dim xlWorksheet As Object
Set xlApp = CreateObject("Excel.Application")
Set xlWorkbook = xlApp.Workbooks.Open(filePath)
Set xlWorksheet = xlWorkbook.Sheets(1)


For i = 2 To xlWorksheet.UsedRange.Rows.Count
    row = xlWorksheet.Rows(i).Value
    rs.AddNew
    For j = 1 To 17
        If j = 4 And InStr(1, row(1, j), "-") > 0 Then
            Dim parts() As String
            parts = Split(row(1, j), "-")
            rs.Fields(j - 1).Value = parts(0)
            rs.Fields(j).Value = parts(1)
            j = j + 1
        ElseIf j < 4 Then
            rs.Fields(j - 1).Value = row(1, j)
        Else
            rs.Fields(j).Value = CStr(row(1, j - 1))
        End If
    Next j
    rs.Update
Next i


xlWorkbook.Close False
xlApp.Quit
Set xlWorksheet = Nothing
Set xlWorkbook = Nothing
Set xlApp = Nothing
rs.Close
db.Close
Set rs = Nothing
Set tbl = Nothing
Set db = Nothing


MsgBox "Data imported successfully.", vbInformation






End Sub

I have a button on a form, where the user selects the file

[Welshgasman]

So use an intermediate table for the import.?
Split the hyphenated field when you move from that table to your real table.

Not sure creating a table all the time is great for bloating the DB?

[jo15765]

I'm not sure what you are suggesting?

Using a temp table for the import so the user can validate the data before importing into the actual production table

[Welshgasman]

No, You bring the data into a temp table and then split that field, then transfer the data you require to your real table.
That, or just split as you display.

I'm against storing that and the split data as well. One or the other.

[jo15765]

ah so I see - split it in acess instead of trying to in the import.

Okay - any thoughts on how to better the import process? Someone said below row/by/row was a bad idea.

[Welshgasman]

https://learn.microsoft.com/en-us/of...d.transfertext

Do it manually once and create a specification if needed.

[CJ_London]

I use sql to import text files.

two basic constructs

SELECT *
FROM (SELECT * FROM [sheet1$A:E] AS xlData IN 'C:\Path\xlName.XLSX'[Excel 12.0;HDR=no;IMEX=0;ACCDB=Yes])  AS XL;

and

SELECT *
FROM [excel 12.0 xml;hdr=yes;imex=1;DATABASE=C:\Path\xlName.XLSX].[Sheet1$] AS T;

I prefer the first method because you can easily apply access functionality to modify the values as required. e.g.

the value could be 111111 or could be 111111-000, if the value is hyphenated, I want to split on the hyphen and everything to the left of the hyphen goes in table field name GymIdNum, and everything to the right of the hyphen goes in GymIdVar

SELECT  fld1, fld2, fld3, iif(instr(fld4,"-")>0,left(fld4,instr(fld4,"-")-1,fld4) AS GymIdNum, iif(instr(fld4,"-")>0,mid(fld4,instr(fld4,"-")+1,"") AS GymIdVar, fld5.....etc

Get your select working as required, then convert the query to an append or make table as required