csv editing

[pcjunki]

I just got hit with a project here at work, and I have no idea where to start, or what to do.
I have a csv file with over 15,000 records we need to import into our database, but I need to do some formatting/editing in a particular column.

when I open it up in excel, it's column D

The data is this column is numbers, if the data has 8 numbers in it, leave it alone, but if it has 7 numbers I want to add a 0 in the very beginning

hope this makes sense, if someone could start me off in the right direction, I would be much appreciated

 

 

[BrewManNH]

Here's one way to do it using a _FileReadToArray and creating a 2D array out of the CSV file.

#include <FileConstants.au3>
#include <File.au3>
#include <Array.au3> ; Only needed for the _ArrayDisplay function

#Region Used to create a test csv file
$hFile = FileOpen("Test.csv", $FO_OVERWRITE)
For $I = 1 To 100
    If Mod($I, 10) Then ; Every 10th line will have a 7 digit number in the 4th column
        FileWriteLine($hFile, "col1,col2,col3,12345678,col5")
    Else
        FileWriteLine($hFile, "col1,col2,col3,1234567,col5")
    EndIf
Next
FileClose($hFile)
#EndRegion

Global $aFileRead
_FileReadToArray("Test.csv", $aFileRead, $FRTA_NOCOUNT, ",")
_ArrayDisplay($aFileRead, "Before")
For $I = 0 to UBound($aFileRead) - 1
    If StringLen($aFileRead[$i][3]) < 8 Then $aFileRead[$i][3] = "0" &  $aFileRead[$i][3]
Next
_ArrayDisplay($aFileRead, "After")

The only part necessary to do it is the section after the #EndRegion, and the File* includes.

I'm sure someone will come up with a RegEx to do it, but at least this way, it's easy to understand.

 

[jchd]

As an alternative you can also create an "on insert" trigger in the DB to do that.

[mikell]

I'm sure someone will come up with a RegEx to do it

StringFormat would be better 

[MuffinMan]

You can do that directly in Excel...

1. Select cells you want formatted in this way.
2. Right click and select "Format Cells..."
3. Select the "Number" Tag, and scroll down to "Custom" in the category list.
4. Type "00000000" into the Type field.

Everything will be zero padded to 8 digits.

[pcjunki]

thanks MuffinMan, that's exactly what I needed!

 

[pcjunki]

wait, I take that back, it just shows it differently, the raw data does not change.

as you can see, in column D the actual value is 5282995

 

Edited October 6, 2015 by pcjunki

[MuffinMan]

I don't see an attached picture, but if you save the data back to a csv file, it WILL retain the zero-padding.  in Excel, it will still show the original value.

[BrewManNH]

Unless the column format is set to Text, no matter what you do Excel will strip the leading zero(es). That's just how spreadsheets work.

If you want the padding to be set to the string, you have to do it before you put it into Excel.