pcjunki

csv editing

9 posts in this topic

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

 

 

Share this post


Link to post
Share on other sites



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. ;)

 


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

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


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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

StringFormat would be better  :D

Share this post


Link to post
Share on other sites

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.

1 person likes this

Share this post


Link to post
Share on other sites

thanks MuffinMan, that's exactly what I needed!

 

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

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 by pcjunki

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now