Extra Blank line in CSV file

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello
I am exporting data from an Access 2000 table to a CSV file for upload to an AS400 application. For some reason the file always has a blank record at the end which causes the AS400 import to reject the file. To solve this I have to manually edit the file before uploading

Any suggestions on how to eliminate the extra record

Thanks

Scott Straus
 
1. Open the file in code after exporting and chop off the extra line.

2. Skip the wizard and export it yourself.

Sample code:

Here is one way to export data to a fixed width file without the wizard:

Some advantages include easy updating of a spec change, easy exporting of
leading zeroes (notoriously difficult by the way) and a simple trick for
right justifying data.


Sub ExportData(strExportFile As String)
'strExportFile is the full path and name where you would like to create
and save the output file
Dim rs As Recordset
Dim strData As String
Dim intFileNum As Integer

'get file handle and open for output
intFileNum = FreeFile()

'opens the disk file
Open strExportFile For Output As #intFileNum

'open the recordset
Set rs = CurrentDb.OpenRecordset("MyTableOrQueryName", dbOpenSnapshot)
'the numbered comments show the fixed width positions
With rs
Do Until .EOF
'this will pad the end of the field with spaces
strData = ![key] & Space(11 - Len(![key])) '1-11
strData = strData & ![TransType] '12

'this is how to right justify an entry
strData = strData & Space(14 - Len(Format(![Qty], "0.0000"))) &
Format(![QtyRcv], "0.0000") '13-26

strData = strData & Format(![TransDate], "mm/dd/yyyy") '27-36
strData = strData & Format(![Date1], "mm/dd/yyyy") '37-46
strData = strData & Format(![Date2], "mm/dd/yyyy") '47-56
strData = strData & Format(![Date3], "mm/dd/yyyy") '57-66
strData = strData & Format(![Date4], "mm/dd/yyyy") '67-76
strData = strData & ![Num] & Space(10 - Len(![Num])) '77-86
strData = strData & ![Status] '87
strData = strData & ![Reason] '88

'write out to file
Print #intFileNum, strData
.MoveNext
Loop
End With

Close #intFileNum
rs.Close
Set rs = Nothing
MsgBox (strExportFile & " has been created.")
End Sub

--
Joe Fallon
Access MVP



sstrauss said:
Hello,
I am exporting data from an Access 2000 table to a CSV file for upload to
Click to expand...
an AS400 application. For some reason the file always has a blank record at
the end which causes the AS400 import to reject the file. To solve this I
have to manually edit the file before uploading.
 
Thanks for you help with this Joe.

In particular I liked your approach on the leading zeros. Originally my requirements were to export my table to a flat file. My numbers were dollar amounts and I had to drop the decimal point and pad with zeros. I ended up writing a function to convert my numbers to the format as I loaded the table.

Here is what I did

Function Zpad(sz As Integer, inpt As Double
'sz is the length of the fiel
'inpt is the number to convert

Dim lgsz As Integer 'length of converted numbe
Dim newno As String 'converted numbe

newno = CStr(Int(inpt * 100)
lgsz = Len(newno
Zpad = String((sz - lgsz), "0") & newn
End Functio

Scott
 
Back
Top