CSV Line Formatting Function

Open Forum > What's New

(1/1)

Support:
This Script BASIC function makes it easy to quickly format raw CSV (comma-separated values) files that has been a standard way of normalizing data for export / import. While searching for sample CSV data to play with I came across a site that posted the Sacramento crime log for January 1st, 2006 (24 hour period)

FYI: You can use either of the numeric formatting masks Script BASIC offers (BASIC or C style) and if you leave out the format info for a column, it will be ignored. This function will be added to the T.bas (Tools) extension module include file for the 2.2 release.

--- Code: Script BASIC ---' result = FormatLine(in_str, fmt_str, quo_char, num_spc) Note: num_spc = -1 uses TAB FUNCTION FormatLine(ln,fmtstr,qc,nsp) SPLITAQ ln BY "," QUOTE qc TO col SPLITA fmtstr BY "|" TO fmtcmd rs = "" FOR x = 0 to UBOUND(col) SPLITA fmtcmd[x] BY ":" TO fmt IF fmt[0] = "L" THEN tmp = LEFT(col[x] & STRING(fmt[1]," "),fmt[1]) GOSUB Margin ELSE IF fmt[0] = "R" THEN IF fmt[2] <> undef THEN tmp = FORMAT(fmt[2],col[x]) ELSE tmp = col[x] END IF tmp = RIGHT(STRING(fmt[1]," ") & tmp, fmt[1]) GOSUB Margin ELSE IF fmt[0] = "C" THEN pad = fmt[1] - LEN(col[x]) pboth = pad \ 2 prt = pad % 2 tmp = STRING(pboth," ") & col[x] & STRING(pboth," ") & STRING(prt," ") GOSUB Margin END IF NEXT GOTO Done   Margin: IF nsp = -1 THEN tmp &= "\t" ELSE tmp &= STRING(nsp," ") END IF rs &= tmp    RETURN Done: FormatLine = rsEND FUNCTION OPEN "SacramentocrimeJanuary2006.csv" FOR INPUT AS #1OPEN "sac.fmt" FOR OUTPUT AS #2fmtstr = "L:15|L:30|R:4|L:4|R:6|L:35|L:6|R:10:%~-##0.0000~|R:10:%~-##0.0000~"LINE INPUT #1, hdrWHILE NOT EOF(1) LINE INPUT #1, csvln csvln = CHOMP(csvln) PRINT #2, FormatLine(csvln,fmtstr,"",2),"\n"WEND   CLOSE(1)CLOSE(2)
Output (7584 rows)

jrs@laptop:~/sb/sb22/test$ time scriba fmtline.sb

real   0m0.454s
user   0m0.415s
sys    0m0.036s
jrs@laptop:~/sb/sb22/test$

--- Code: ---1/1/06 0:00 3108 OCCIDENTAL DR 3 3C 1115 10851(A)VC TAKE VEH W/O OWNER 2404 38.5504 -121.3914
1/1/06 0:00 2082 EXPEDITION WAY 5 5A 1512 459 PC BURGLARY RESIDENCE 2204 38.4735 -121.4902
1/1/06 0:00 4 PALEN CT 2 2A 212 10851(A)VC TAKE VEH W/O OWNER 2404 38.6578 -121.4621
1/1/06 0:00 22 BECKFORD CT 6 6C 1443 476 PC PASS FICTICIOUS CHECK 2501 38.5068 -121.4270
1/1/06 0:00 3421 AUBURN BLVD 2 2A 508 459 PC BURGLARY-UNSPECIFIED 2299 38.6374 -121.3846

--- End code ---

Support:
I thought I would take this another step and create a CSV2SQL function for the T.bas (Tools) extension module include file. This is just my proof of concept attempt and I'll make a function call out of the following example for the 2.2 release.

I attached an example of the final CSV2SQL SUB running on Android Linux native.

--- Code: Script BASIC ---IMPORT sqlite.bas OPEN "SacramentocrimeJanuary2006.csv" FOR INPUT AS #1db = sqlite::open("sac116.db")fmtstr = "SSISISIRR"LINE INPUT #1, hdrhdr = CHOMP(hdr)SPLITA hdr BY "," TO colSPLITA fmtstr BY "" TO typlastcol = UBOUND(col)sql = "CREATE TABLE crime ("FOR x = 0 TO lastcol tmp = "" IF typ[x] = "S" THEN tstr = " TEXT" ELSE IF typ[x] = "I" THEN tstr = " INTEGER" ELSE IF typ[x] = "R" THEN tstr = " REAL" END IF tmp &= col[x] & tstr IF x <> lastcol THEN tmp &= ", " sql &= tmpNEXTsql &= ");"sqlite::execute(db, sql)sqlite::execute(db, "BEGIN TRANSACTION")WHILE NOT EOF(1) sql = "INSERT INTO crime VALUES (" LINE INPUT #1, csvln csvln = CHOMP(csvln) SPLITAQ csvln BY "," QUOTE "" TO col FOR x = 0 TO lastcol IF typ[x] = "S" THEN tmp = "'" & col[x] & "'" ELSE tmp = col[x] END IF IF x <> lastcol THEN tmp &= ", " sql &= tmp NEXT sql &= ");" sqlite::execute(db, sql)WENDsqlite::execute(db, "END TRANSACTION")sqlite::close(db)CLOSE(1)
Output

jrs@laptop:~/sb/sb22/test$ time scriba csv2sql.sb

real   0m0.763s
user   0m0.457s
sys    0m0.016s
jrs@laptop:~/sb/sb22/test$ sqlite3
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .open sac116.db
sqlite> SELECT COUNT(*) FROM crime;
7584
sqlite> .q
jrs@laptop:~/sb/sb22/test$

Support:
I modified the fmtline() function to support either CSV or SQLite3 rows.

--- Code: Script BASIC ---' result = FormatLine(in_str/array, fmt_str, quo_char, num_spc) Note: num_spc = -1 uses TAB IMPORT sqlite.bas FUNCTION FormatLine(ln,fmtstr,qc,nsp) IF ISARRAY(ln) THEN col = ln ELSE SPLITAQ ln BY "," QUOTE qc TO col END IF SPLITA fmtstr BY "|" TO fmtcmd rs = "" FOR x = 0 to UBOUND(col) SPLITA fmtcmd[x] BY ":" TO fmt IF fmt[0] = "L" THEN tmp = LEFT(col[x] & STRING(fmt[1]," "),fmt[1]) GOSUB Margin ELSE IF fmt[0] = "R" THEN IF fmt[2] <> undef THEN tmp = FORMAT(fmt[2],col[x]) ELSE tmp = col[x] END IF tmp = RIGHT(STRING(fmt[1]," ") & tmp, fmt[1]) GOSUB Margin ELSE IF fmt[0] = "C" THEN pad = fmt[1] - LEN(col[x]) pboth = pad \ 2 prt = pad % 2 tmp = STRING(pboth," ") & col[x] & STRING(pboth," ") & STRING(prt," ") GOSUB Margin END IF NEXT GOTO Done Margin: IF nsp = -1 THEN tmp &= "\t" ELSE tmp &= STRING(nsp," ") END IF rs &= tmp RETURN Done: FormatLine = rsEND FUNCTION db = sqlite::open("sac16.db")stmt = sqlite::query(db,"SELECT * FROM crime LIMIT 1")sqlite::row(stmt)sqlite::FetchArray(stmt,columns)fmtstr = "L:15|L:30|R:4|L:4|R:6|L:35|L:6|R:10:%~-##0.0000~|R:10:%~-##0.0000~"PRINT FormatLine(columns,fmtstr,"",2),"\n"sqlite::close(db)
Output

--- Code: ---jrs@laptop:~/sb/sb22/test$ scriba fmtsqlrow.sb
1/1/06 0:00 3108 OCCIDENTAL DR 3 3C 1115 10851(A)VC TAKE VEH W/O OWNER 2404 38.5504 -121.3914
jrs@laptop:~/sb/sb22/test$

--- End code ---

Navigation

[0] Message Index

Go to full version