TheAntiGates
10-20-2007, 10:40 AM
I'm importing a .CSV and then exporting a sorted query of it. Unfortunately the exported CSV has occasions of
,,
(just commas) instead of
,"",
or
," ",
but I want the quotation marks on every field in the exported CSV.
I'm thinking about setting required = true and a default value "" for each field in the recipient table (which has 0 records but has the fields named). There are hundreds of fields. I'm weak on Access objects, so if that is the ticket, I'm looking to you for the code.
Perhaps there are parameters I can simply set on a statement that imports the .CSV in code? I manually import it now.
Failing that, I'll build an update query - still a lot of work. I hoped the code answer would be a compact loop walking through each field in the recordset.
Note that every field is text, which is fine.
Here's code that works fantastically for fixed width records, but I don't know how to adapt it for reading a CSV, if that inspires anyone. Note that this technique uses Trim to prevent a ton of blanks, reducing the .MDB size by about one-half!Sub ImportTextFile()
'import a fixed width text file into a new table called "Newtable" (you must rename it afterwards)
'LOOK BETWEEN THE ASTERISKS BELOW for variable info
Dim db As Database
Dim rs As Recordset
Dim tdfNewtable As TableDef, fldNew As Field
Dim strData As String, strTemp As String
Dim strDirFileName As String
Dim fieldnames, fieldlengths 'as is, so valid as variants for Array function
Dim i As Long, iFieldcount As Long, iRunningTotal As Long, iRecordCount As Long
'************************************************************
strDirFileName = "c:\dUMMY." 'text file source
fieldnames = Array("fld1", "fld2", "fld3", "fld4")
fieldlengths = Array(1, 1, 8, 8)
#Const TABLE_PREEXISTS = False
'************************************************************
iFieldcount = UBound(fieldnames)
Set db = CurrentDb
Close #1
Open strDirFileName For Input As #1 'open the file for input
#If Not TABLE_PREEXISTS Then
Set tdfNewtable = db.CreateTableDef("Newtable")
With tdfNewtable
' Create and append new Field objects to the table.
For i = 0 To iFieldcount
Set fldNew = .CreateField(fieldnames(i))
fldNew.Size = fieldlengths(i)
fldNew.Type = dbText
.Fields.Append fldNew
Next i
End With
db.TableDefs.Append tdfNewtable 'this actually creates the table described above
#End If
Set rs = db.OpenRecordset("Newtable", dbOpenDynaset)
Do Until EOF(1)
Line Input #1, strData
rs.AddNew 'get set for new data
iRunningTotal = 1 'because "Mid" function is 1-based
For i = 0 To iFieldcount
strTemp = Mid(strData, iRunningTotal, fieldlengths(i))
rs.Fields(i) = Trim(strTemp)
If strTemp = "" Then strTemp = " "
iRunningTotal = iRunningTotal + fieldlengths(i)
Next i
rs.Update
iRecordCount = iRecordCount + 1
If 0 = iRecordCount Mod 10000 Then Debug.Print iRecordCount & " records so far"
Loop
Close #1
Debug.Print iRecordCount & " records - job complete"
ProcExit:
rs.Close: Set rs = Nothing: Set db = Nothing
End Sub
,,
(just commas) instead of
,"",
or
," ",
but I want the quotation marks on every field in the exported CSV.
I'm thinking about setting required = true and a default value "" for each field in the recipient table (which has 0 records but has the fields named). There are hundreds of fields. I'm weak on Access objects, so if that is the ticket, I'm looking to you for the code.
Perhaps there are parameters I can simply set on a statement that imports the .CSV in code? I manually import it now.
Failing that, I'll build an update query - still a lot of work. I hoped the code answer would be a compact loop walking through each field in the recordset.
Note that every field is text, which is fine.
Here's code that works fantastically for fixed width records, but I don't know how to adapt it for reading a CSV, if that inspires anyone. Note that this technique uses Trim to prevent a ton of blanks, reducing the .MDB size by about one-half!Sub ImportTextFile()
'import a fixed width text file into a new table called "Newtable" (you must rename it afterwards)
'LOOK BETWEEN THE ASTERISKS BELOW for variable info
Dim db As Database
Dim rs As Recordset
Dim tdfNewtable As TableDef, fldNew As Field
Dim strData As String, strTemp As String
Dim strDirFileName As String
Dim fieldnames, fieldlengths 'as is, so valid as variants for Array function
Dim i As Long, iFieldcount As Long, iRunningTotal As Long, iRecordCount As Long
'************************************************************
strDirFileName = "c:\dUMMY." 'text file source
fieldnames = Array("fld1", "fld2", "fld3", "fld4")
fieldlengths = Array(1, 1, 8, 8)
#Const TABLE_PREEXISTS = False
'************************************************************
iFieldcount = UBound(fieldnames)
Set db = CurrentDb
Close #1
Open strDirFileName For Input As #1 'open the file for input
#If Not TABLE_PREEXISTS Then
Set tdfNewtable = db.CreateTableDef("Newtable")
With tdfNewtable
' Create and append new Field objects to the table.
For i = 0 To iFieldcount
Set fldNew = .CreateField(fieldnames(i))
fldNew.Size = fieldlengths(i)
fldNew.Type = dbText
.Fields.Append fldNew
Next i
End With
db.TableDefs.Append tdfNewtable 'this actually creates the table described above
#End If
Set rs = db.OpenRecordset("Newtable", dbOpenDynaset)
Do Until EOF(1)
Line Input #1, strData
rs.AddNew 'get set for new data
iRunningTotal = 1 'because "Mid" function is 1-based
For i = 0 To iFieldcount
strTemp = Mid(strData, iRunningTotal, fieldlengths(i))
rs.Fields(i) = Trim(strTemp)
If strTemp = "" Then strTemp = " "
iRunningTotal = iRunningTotal + fieldlengths(i)
Next i
rs.Update
iRecordCount = iRecordCount + 1
If 0 = iRecordCount Mod 10000 Then Debug.Print iRecordCount & " records so far"
Loop
Close #1
Debug.Print iRecordCount & " records - job complete"
ProcExit:
rs.Close: Set rs = Nothing: Set db = Nothing
End Sub