Working with CSV files - importing > manipulating > exporting 2

[PureSoft]

Hello Guys,

I have a website order export as a raw csv file with the various fields one after the other, with a separate line for each order. I want to read in that file, assigning each field to a variable so I can analyse those variables and then do some manipulations, before finally exporting to a new csv file to have it in an appropriate format for import to an accounts package called Quickbooks.

I'm right at the beginning of this, so at present I just want to be able to read in the csv file to the variables for writing straight back out into a new file - as a first step.

I searched the posts on 'csv' and found some pointers for starters, but I'm having trouble using the 'Input' command to read in the csv file to the variables, since the source file varies in the number of fields depending on whether the product in question has some additional product options fields passed over also.

I thought maybe I could say add a few extra holding variables and just read in, with the Input command just reading in blank values at the end of the line, but it seems to jump down and start reading in the fields of the next line.

Here is the code I've used forthe first simple test - its reading in 50 fields (46 fields from the first line, but then the first 4 fields of the second line also).

===============================================
Private Sub Command1_Click()

Open "D:\orders.csv" For Input As #1
Input #1, Field1, Field1, Field2, ... , Field50
Close #1

Open "D:\QB_Import.csv" For Output As #1
Write #1, Field1, Field2, ... , Field50
Close #1

End Sub
===============================================

Is there a way to make that work or can I use some other command or way of doing this. Can I use some sort of end of line test and count along the fields of a row and copy them into an array for storage, using that counter as the array pointer to extract the correct number of items back from that array?

I'm a newbie to VB, so I don't really know the command names to read more about. Any help with an example snippet of code or pointers on the appropriate commands I should be looking at, would be greatly appreciated.

Thanks for your help.

Philip.

 

[JeffTullin]

You could use Line Input to read the whole of each line, then pass what you get into the SPLIT function, which returns an array with the values separated for you.

dim theArray()
theArray = Split(TheString,",&quot
msgbox MyBuffer(1)...etc



Alternatively, you could set up a TEXT ODBC link to the file, and read it in like a database.

 

[PureSoft]

Thanks for the pointer.

I have now got the basics nearly there, though I am still having two problems:

1) I am using the write statement, reading from the array, but the field outputs from each line are placed on successive lines, rather than one long line - see snippet below. What ways are there to make it just add to the last position in the line rather than starting a new line for the output of each element of the aray?

=============================
i = 0
While i < UBound(hold_values)
Write #2, hold_values(i)
i = i + 1
Wend
=============================

2) I am using an array to store the values of each field in the import file, however it seems that the size of the 'hold_values' array that I create, has a fixed size of when it is first used, i.e. the number of fields of the first line of the import file. I notice that when the next line and last lines of my import file, which happen to be one field longer than the first line, it still seems to use the size that was created for the array initially, so date is not imported for those last fields. Here is the full code for reference:

=========================================
Private Sub Command1_Click()

Open &quot;D:\orders.csv&quot; For Input As #1
Open &quot;D:\QB_Import.csv&quot; For Output As #2

While Not EOF(1)

Line Input #1, Field1
strStripped = Replace(Field1, Chr(34), &quot;&quot
hold_values = Split(strStripped, &quot;,&quot

i = 0
While i < UBound(hold_values)
Write #2, hold_values(i)
i = i + 1
Wend

Write #2, &quot;BREAK&quot; 'comments the csv to help me inspect it.

Wend

Close #1
Close #2

End Sub
========================================

Can anyone suggest a way around this?

Thanks, Philip.

 

[lockesurf]

I spend too many hours a day programming, and currently I am pretty busy, however I could finish what you need in about 10 minutes if I had too.
Since I am doing this out of the kindness of my heart, i will just give you a simple program that I used to do this once.

this program will read the CSV file, into an excel file...
Good Luck... heres how to do it..


First make an new VB project.
Add three radio buttons

###### THESE OPTIONS LET YOU CHANGE YOUR DELIMITTER######
Option1 ###label this &quot;,&quot; CSV file
Option2 ###Label this / Tab format
#######################################################
add a command button
name it:
cmdCreateXLS

#######################################################
also, for this to work, you will need the file that you are opened in the same directory as your visual basic project, call it phones.txt and paste the following CSV info into it:

&quot;John DOE&quot;,&quot;San Diego&quot;,&quot;California&quot;,&quot;Programmer&quot;,&quot;1234595969&quot;
&quot;John SMART&quot;,&quot;San Diego&quot;,&quot;California&quot;,&quot;Programmer&quot;,&quot;1234595969&quot;
&quot;John LAME&quot;,&quot;San Diego&quot;,&quot;California&quot;,&quot;Programmer&quot;,&quot;1234595969&quot;


########################################

now do a view code on your form, and simply paste the following code into it.
then click save project, and then file -- make--.exe


Option Explicit

Private Declare Function ShellExecute Lib &quot;shell32.dll&quot; Alias &quot;ShellExecuteA&quot; (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Dim xlApp As Excel.Application
Dim xlsWorkBook As Excel.Workbook
Dim xlsWorkSheet As Excel.Worksheet
Dim J As Integer
Dim delim As Variant
Dim LineText(8) As String
Dim NewLine As String
Dim CC As String
Dim DD As String
Dim AA
Dim I As Integer
Dim W As Integer
Dim Z As Integer
Dim ReturnCode As Integer

Private Sub cmdCreateXLS_Click()

Me.MousePointer = 11
DD = &quot;&quot;&quot;&quot;
On Local Error Resume Next
Set xlApp = New Excel.Application
Set xlsWorkBook = xlApp.Workbooks.Add
Set xlsWorkSheet = xlsWorkBook.Worksheets.Add
If Option1(0).Value = True Then delim = &quot;&quot;&quot;,&quot;&quot;&quot;
If Option1(1).Value = True Then delim = &quot;*&quot;
If Option1(2).Value = True Then delim = vbTab
xlsWorkSheet.Cells(1, 1).Value = &quot;Name&quot;
xlsWorkSheet.Cells(1, 2).Value = &quot;City&quot;
xlsWorkSheet.Cells(1, 3).Value = &quot;Country&quot;
xlsWorkSheet.Cells(1, 4).Value = &quot;Profession&quot;
xlsWorkSheet.Cells(1, 5).Value = &quot;Phone&quot;
xlsWorkSheet.Cells(1, 1).Font.Bold = True
xlsWorkSheet.Cells(1, 2).Font.Bold = True
xlsWorkSheet.Cells(1, 3).Font.Bold = True
xlsWorkSheet.Cells(1, 4).Font.Bold = True
xlsWorkSheet.Cells(1, 5).Font.Bold = True
I = 1
Open App.Path & &quot;\Phones.txt&quot; For Input As #1
Do While Not EOF(1)
Line Input #1, NewLine
I = I + 1
AA = Split(NewLine, delim)
For Z = LBound(AA) To UBound(AA)
xlsWorkSheet.Cells(I, Z + 1).Value = Replace(AA(Z), DD, &quot;&quot
Next Z
Loop
Close #1
xlsWorkSheet.Columns.AutoFit
xlsWorkSheet.Cells(1, 1).Interior.ColorIndex = 20
xlsWorkSheet.Cells(1, 2).Interior.ColorIndex = 20
xlsWorkSheet.Cells(1, 3).Interior.ColorIndex = 20
xlsWorkSheet.Cells(1, 4).Interior.ColorIndex = 20
xlsWorkSheet.Cells(1, 5).Interior.ColorIndex = 20
xlsWorkSheet.Name = &quot;Phones&quot;
xlsWorkSheet.SaveAs App.Path & &quot;\Phones.xls&quot;
xlApp.Quit
Set xlsWorkSheet = Nothing
Set xlsWorkBook = Nothing
Set xlApp = Nothing
Me.MousePointer = 0
ReturnCode = ShellExecute(hwnd, &quot;Open&quot;, App.Path & &quot;\Phones.xls&quot;, &quot;&quot;, App.Path, 1)
End Sub

Private Sub Option1_Click(Index As Integer)
cmdCreateXLS.Enabled = True
End Sub




THere you go
John L.
johnwlocke@earthlink.net
for more services , you can email me...

 

[lockesurf]

Philip:

Make sure to fix the name of th option buttons,,,
notice the code has them referenced as:
If Option1(0).Value = True Then delim = &quot;&quot;&quot;,&quot;&quot;&quot;
If Option1(1).Value = True Then delim = &quot;*&quot;
If Option1(2).Value = True Then delim = vbTab

and i had you name them option1 and option2,
you will need to fix that for it to run right...
john L.

 

[PureSoft]

John,

Thanks for the reply, and the example using the direct access of Excel, though I'm wanting to do it avoiding the need for Excel to be installed and resident on the host PC, since I will want to offer this finished conversion utility to other users who have the same shopping cart system, so I think it would be best if its completely independent, just processing the raw text itself.

Thanks, Philip.

 

[lockesurf]

Philip:
Precisely, you can take they excel file creation part out of that code, and create a new text file, and write line by line, instead of filling an excel record by record, i was hoping you could read between the lines, and see that the engine is there for you , you have the csv file parser, now just write it to a file, i just showed you how to write it to an excel,,, get creative,
thanks
john L.

 

[PureSoft]

John,

Thanks again for replying, but I want to be able to write record by record (or alternatively a line at a time created from my cusomised records) becuase I'm going to have to insert custom fileds that I'm creating dynamically from the original file content - I'll be doing things like adding in additional tax fields with associated tax values calculated from some of the input fields, outputting everyhting in a different order and format.

I have the parser code already worked out above, its the means of getting the array of information into a single string of text that I want to work out.

I looked through the code, and I understand how the loop works to put the code in using Excel, but I want to find the file output formatting option to get my custom variables and/or array onto the one row of a csv file, avoiding Excel.

BTW I sorted out my problem with the number of fileds not appearing correctly - I was using the test While i < UBound(hold_values), in stead of While i <= UBound(hold_values).

I've now used the example you gave of For i = LBound(hold_values) To UBound(hold_values) since it seems a nicer way to do it (it helped me catch that logic eror I made too).

Thanks, Philip.

 

[PureSoft]

ok, got it sorted out. Used the string '&' function to concantenate my array elements together, adding in the required formatting for the csv. Needed to use the Print command as opposed to the Write command.

Here is the working code for anyone else dropping by:

Private Sub Command1_Click()

Open &quot;D:\import.csv&quot; For Input As #1
Open &quot;D:\export.csv&quot; For Output As #2

While Not EOF(1)

Line Input #1, Field1
strStripped = Replace(Field1, Chr(34), &quot;&quot
hold_values = Split(strStripped, &quot;,&quot

strOutput = &quot;&quot;
For i = LBound(hold_values) To UBound(hold_values)
strOutput = strOutput & Chr(34) & hold_values(i) & Chr(34) & &quot;,&quot;
Next

Print #2, strOutput

Wend

Close #1
Close #2

End Sub

Thanks, Philip