Export/Import csv issue in ACCESS VBA
HI all,
I have what I think is exactly the same code in two databases, but I get different results.
I first export 4 tables to .csv files, then I import the the 4 /csv files.
In one database it all works fine, but in another the import seems to add a blank record for each of the tables. The 4 tables are linked with 1 to 1 relationships and only one of the tables has a key field that is an autoincrement. In the problem database, I get an error that there is a null value in the key field of the table with the autoincrement value.
I found a work around, but I would like to understand what is sometimes happening and other times not. In the code section I have included the import csv code that causes the problem and the workaround. The workaround just deletes all records with null values in the linking field. I have also included an image that shows the extra record that is added during the csv import.
I have what I think is exactly the same code in two databases, but I get different results.
I first export 4 tables to .csv files, then I import the the 4 /csv files.
In one database it all works fine, but in another the import seems to add a blank record for each of the tables. The 4 tables are linked with 1 to 1 relationships and only one of the tables has a key field that is an autoincrement. In the problem database, I get an error that there is a null value in the key field of the table with the autoincrement value.
I found a work around, but I would like to understand what is sometimes happening and other times not. In the code section I have included the import csv code that causes the problem and the workaround. The workaround just deletes all records with null values in the linking field. I have also included an image that shows the extra record that is added during the csv import.
This is the code that adds an empty record.
DoCmd.RunSQL ("delete from wetform") 'this empties all the tables
DoCmd.TransferText acImportDelim, , "WetForm", "C:\WetForm\WetForm" & mvinnerext, True
DoCmd.TransferText acImportDelim, , "WetVeg", "C:\WetForm\WetVeg" & mvinnerext, True
DoCmd.TransferText acImportDelim, , "WetHyd", "C:\WetForm\WetHyd" & mvinnerext, True
DoCmd.TransferText acImportDelim, , "WetSoil", "C:\WetForm\WetSoil" & mvinnerext, True
This is the code that works because it deletes the empty records.
DoCmd.RunSQL ("delete from wetform")
DoCmd.TransferText acImportDelim, , "WetForm", "C:\WetForm\WetForm" & mvinnerext, True
DoCmd.RunSQL ("delete from wetform where isnull(ID1)")
DoCmd.RunSQL ("delete from wetveg")
DoCmd.TransferText acImportDelim, , "WetVeg", "C:\WetForm\WetVeg" & mvinnerext, True
DoCmd.RunSQL ("delete from wetveg where isnull(ID2)")
DoCmd.RunSQL ("delete from wethyd")
DoCmd.TransferText acImportDelim, , "WetHyd", "C:\WetForm\WetHyd" & mvinnerext, True
DoCmd.RunSQL ("delete from wethyd where isnull(ID3)")
DoCmd.RunSQL ("delete from wetsoil")
DoCmd.TransferText acImportDelim, , "WetSoil", "C:\WetForm\WetSoil" & mvinnerext, True
DoCmd.RunSQL ("delete from wetsoil where isnull(ID4)")
Also make sure there is nothing funky in your import spec. (Recreate it, to be sure)
ASKER
The .csv file defiinitely has the field names. What I think is happening as far as the first empty record is that all the tables are having a blank record added, or created somewhere. The one table with the key rejects the record because the key field is null. That means the other 1 to 1 tables that don't have a key field are left with the empty record. That means the 1 to 1 relationship is broken. I am attaching all 4 of the files.
The program that created the files occurs on a PDA and is written in VB I am including the code for that. The actuall .csv files had one extra empty line, but I acually deleted those manually from each of the 4 files to see if that was the problem, but I had the same problem. The True/False section just converts the text field on the PDA to a -1/0 field on the .csv for import into ACCESS.
The program that created the files occurs on a PDA and is written in VB I am including the code for that. The actuall .csv files had one extra empty line, but I acually deleted those manually from each of the 4 files to see if that was the problem, but I had the same problem. The True/False section just converts the text field on the PDA to a -1/0 field on the .csv for import into ACCESS.
cmd.CommandText = "SELECT PlotIDHyd,ID3,HydIndSurfWaterA1,HydIndHighWatTableA2,HydIndSat12inA3,HydIndWaterMarkB1,HydIndSedDepB2,HydIndDriftDepB3,HydIndAlgMatB4,HydIndIrnDepB5,HydIndSurfSoilCrackB6,HydIndInundAerialPhotB7,HydIndSparVegB8,HydIndWatStainLvsB9,HydIndDrainPattB10,HydIndAquaticInvertB13,HydIndTrueAqPltsB14,HydIndMossTrLineB16,HydIndHSOdorC1,HydIndDryWatTableC2,HydIndOxyrootC3,HydIndFeReducedC4,HydIndFeRedPlowedSoilsC6,HydIndThinMuckC7,HydIndCrayfishBurrC8,HydIndSatVisAerialImgC9,HydIndStntStrsPtsD1,HydIndGeoPosD2,HydIndShallowAquitardD3,HydIndMicTopoRlfD4,HydIndFACNeutralD5,HydIndOther,HydSurfWater,HydDepthSurfaceW,HydWatTable,HydDepthPitWater,HydSatSoil,HydDepthSatSoil,HydRecordedData,HydRemarks FROM WetHyd"
Dim tblDataHyd As New DataTable
ad.SelectCommand = cmd
ad.Fill(tblDataHyd)
obj = New StreamWriter("\My Documents\WetHydEMP" & Now.Date.ToString("yyyyMMdd") & ".csv")
strCols = ""
nColCount = tblDataHyd.Columns.Count
obj.WriteLine("PlotIDHyd,ID3,HydIndSurfWaterA1,HydIndHighWatTableA2,HydIndSat12inA3,HydIndWaterMarkB1,HydIndSedDepB2,HydIndDriftDepB3,HydIndAlgMatB4,HydIndIrnDepB5,HydIndSurfSoilCrackB6,HydIndInundAerialPhotB7,HydIndSparVegB8,HydIndWatStainLvsB9,HydIndDrainPattB10,HydIndAquaticInvertB13,HydIndTrueAqPltsB14,HydIndMossTrLineB16,HydIndHSOdorC1,HydIndDryWatTableC2,HydIndOxyrootC3,HydIndFeReducedC4,HydIndFeRedPlowedSoilsC6,HydIndThinMuckC7,HydIndCrayfishBurrC8,HydIndSatVisAerialImgC9,HydIndStntStrsPtsD1,HydIndGeoPosD2,HydIndShallowAquitardD3,HydIndMicTopoRlfD4,HydIndFACNeutralD5,HydIndOther,HydSurfWater,HydDepthSurfaceW,HydWatTable,HydDepthPitWater,HydSatSoil,HydDepthSatSoil,HydRecordedData,HydRemarks")
With tblDataHyd.Rows.GetEnumerator
While .MoveNext
Dim dr As Data.DataRow = CType(.Current, Data.DataRow)
strCols = ""
Dim nCol As Integer = 0
For nCol = 0 To nColCount - 1
If dr.Item(nCol).ToString.ToUpper = "FALSE" Then
strCols = strCols & """" & "0" & ""","
ElseIf dr.Item(nCol).ToString.ToUpper = "TRUE" Then
strCols = strCols & """" & "-1" & ""","
Else
strCols = strCols & """" & dr.Item(nCol) & ""","
End If
Next
strCols = strCols.Substring(0, strCols.Length - 1)
obj.WriteLine(strCols)
End While
End With
obj.Close()
From what I can tell, there is not that much data in the csv files.
What data there is appears to be sporadic...
Try deleting the Text Qualifier from the Import Spec
What data there is appears to be sporadic...
Try deleting the Text Qualifier from the Import Spec
ASKER
The data files are just small demos, there can be many records in reality. I think I need the text qualifier just to allow the use of commas and some other reasons. I didn't do much of the PDA programming. the weird thing is that this same code seems to work fine in other situations. The real question is How is an extra record getting created during the .csv import. Like I mentioned, I have a work around that jsut directly deletes any records with a null value in the linking fields, ID1, ID2, ID3, ID4.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Thanks for trying. I will wait a while then just divide up points. I was hoping there was some switch that would remove a mystery CRLF or something. Or some trick in acImportDelim that dumped empty record at the beginning or end of a record set.
ASKER
Not the commentors fault, still and unknown cause, but a workaround was presented.
You are setting the "HasFieldnames" argument to True
But perhaps Row 1 does not contain the field names...