Import a csv file 1 line at a time, modify the data and write to a spreadsheet.

I need to read a .csv file with about 800 fields per row into excel, one line at a time and based on the data in the field, modify that information and write it to an excel spreadsheet.  I've included a sample file with header records in the first row, for example the value in the monitor field of the first record is 1, I want to change that to Yes.

Can someone show me how to do it on that field and I will be able to do the rest.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Amac227Author Commented:
Importing the file isn't the problem, I can get the information into excel.  I need to modify the data before I write it to the spreadsheet.  I can modify it in the spreadsheet, but that would mean putting formulas in each of the 800 x (# of user records) cells.
Martin LissOlder than dirtCommented:
I don't have the time right now to provide the code but you could write a macro that
  • Opens the csv file as a text file
  • Reads it line by line and allows you to
  • Transfer it's data to the next row in a sheet, or edit that line and then transfer it's data to the next row in a sheet
  • Close the file
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ejgil HedegaardCommented:
It could be something like this

Option Explicit

Sub ImportCsv()
    Dim fName As String
    Dim Text As String
    Dim arText() As String
    Dim i As Integer
    fName = "C:\Test\Test.csv"
    Open fName For Input As #1
    Do Until EOF(1)
        Line Input #1, Text
        arText = Split(Text, ",")
        For i = LBound(arText, 1) To UBound(arText, 1)
            Text = arText(i)
            'do what you want to do for each field
        Next i
    Close #1
End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fabrice LambertFabrice LambertCommented:
800 fields 0_o !!!!

Well, not sure this will work with that much fields, but you can try loading your CSV into a 2D array, editing data will be easyer.

And by respect to SRP, handle loading data and editing them into 2 separate functions.
public function readFile(byval path as string) as variant()
    Dim fso As Object
    Set fso = createObject("Scripting.FileSystemObject")

    Dim stream As Object
    set Stream = fso.openTextStream(path)

    Dim data() as variant
    while not Stream.EOF
        dim maxSize as integer
        maxSize = ubound(data)
        data(maxsize) = split(Stream.readline, ",")
        redim preserve data(maxSize +1)
    readFile = data
    set Stream = nothing
    set fso = Nothing
End Function

public sub updateData(byref data() as variant)
    '// insert whatever code editing datas here
end sub

Open in new window

Finally, transfering data to Excel is very easy and can be done with a single instruction, since range can be assigned to 2D arrays (and vice-versa), as long as the array and targeted range have the same size (ie: same amount of columns and rows):
Dim wb as Excel.worksbook
Set wb = ThisWorkbook

Dim ws as Excel.Worksheet
Set ws = wb.Worksheets(1)

Dim rng As Excel.Range
Set rng = ws.range("A1:V480")
rng.value = data

Set rng = Nothing
Set ws = Nothing
Set wb = Nothing

Open in new window

Hi Andy,

Just confirming that you have 800 fields per record?  That is phenomenal - in 25 years, I don't recall ever seeing a database table with that many fields.

Regardless, you will need to 'manipulate' the data from the CSV to get it into the Excel worksheet that you want to have as the final destination.  Are you wanting all of those fields in Excel at the end, or just a few of them?

Once option would be to open the CSV in Excel, then have a second workbook, that pulls in only the data you want - that might be as simple as links with no additional formulae required (depends on the source data formats and what you need).

Then you save the destination file, close the CSV, and the destination file will contain the data you want. Yuo can either leave it as links (but don't update), or copy / paste to values to fix it and break the link to the source CSV.

Would that solve your problem?

Amac227Author Commented:
Yep, all 832 columns.  It's user information but I'll only need about 750 of the columns. I'm working in VBA so I can code it once and be finished with it, until they change the source file...

Same if you do it in a worksheet - you do it once, and be finished with it (until they change the source format!), plus you have the advantages of no VBA (although you may already have some in which case that benefit won't apply), plus a self documenting audit trail.

Either way, sounds like a hoary data set :-)

I've included a sample file with header records in the first row
I don't see any such file.
Amac227Author Commented:
Thanks for the help, with both of your help
Martin LissOlder than dirtCommented:
It’s possible that you made a mistake when you chose the people who helped you, since while you said “both of your help” you chose one of us and yourself. Let me know if you need help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.