We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question
0

how do you get ALL fields in csv to be double quoted - even blank flields - like "". SQLite will not import ,, must be ,"",

asked 2020-07-18 21:54:35 +0200

kasloman gravatar image

Example:

1. INSERT INTO cdata (First_Name,Last_Name,Display_Name,Primary_Email,Secondary_Email,Phone1,Phone2,Phone3,Address,City,State,ZipCode,Country,Address2,Notes) VALUES ("US Post Office","Gov","Gov, US Post Office",,,"2082632338",,,,,,,,,,);

2. INSERT INTO cdata (First_Name,Last_Name,Display_Name,Primary_Email,Secondary_Email,Phone1,Phone2,Phone3,Address,City,State,ZipCode,Country,Address2,Notes) VALUES ("US Post Office","Gov","Gov, US Post Office","","","2082632338","","","","","","","","","");

  1. will not work importing into SQLite with blank fields ,,,,,,,,,

  2. does work with ,"","","","","",""

edit retag flag offensive close merge delete

Comments

You seem to ask a wrong question. You ask something ("how do you get ALL fields in csv to be double quoted") without the actually relevant part, which seems to be "How do I properly import CSV data into a SQLite DB ... I currently use this ... I open the CSV and use each its line verbatim in an SQL INSERT statement ...".

For which you might get much better responses.

Mike Kaganski gravatar imageMike Kaganski ( 2020-07-19 13:52:34 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2020-07-19 15:30:30 +0200

Lupp gravatar image

updated 2020-07-19 15:47:44 +0200

Needing a specialized variant of writing to csv may justify to use specialized code. Fortunately it's really simple to do the task with a few lines of Basic code, if no parametrization is needed. See the code below for an exampßle:

Sub extremelySimplified()
doc = ThisComponent : sheets = doc.Sheets
trgFilePath = InputBox("Asking for target file", _
              "Please enter the complete path of the csv to write: ", doc.URL)
If trgFilePath= doc.URL Then trgFilePath = trgFilePath & ".csv"
trgFilePath = ConvertToUrl(trgFilePath)
quoteCharacter = Chr(34) : separator = ";" REM The semicolon should be preferred if not ...
REM The quoteCharacter must be assured to not occur inside a data value.
srcSheetName = InputBox("Asking for source sheet", _
               "Please enter the name of the source sheet: ", sheets(0).Name) 
dataSheet = sheets.getByName(srcSheetName)
cellCur = dataSheet.createCursor() : cellCur.gotoStartOfUsedArea(False) : cellCur.gotoEndOfUsedArea(True)
dataArr = cellCur.getDataArray()   : uR = Ubound(dataArr)               : uC = Ubound(dataArr(0))
Dim lines(uR )
For r = 0 To uR
  For c = 0 To uC
    If TypeName(dataArr(r)(c))="Double" Then
      REM Numbers are written as formatted for the sheet.
      dataArr(r)(c) = quoteCharacter & _
                      cellCur.getCellByPosition(c, r).String & quoteCharacter
    Else
      dataArr(r)(c) = quoteCharacter & dataArr(r)(c) & quoteCharacter
    End If
  Next c
  lines(r) = Join(dataArr(r), separator)
Next r
fn = FreeFile
Open trgFilePath For Output As #fn
For r = 0 To uR
  Print #fn, lines(r)
Next r
Close #fn 
End Sub

Adapt the details as needed.

(Just had another look and found I had written "Asc(34)" in place of the correct "Chr(34)". Now fixed.)

edit flag offensive delete link more
0

answered 2020-07-19 10:16:14 +0200

keme gravatar image

updated 2020-07-19 10:16:41 +0200

There is https://github.com/open-ods/import_tool, which I have not tried.

You could also make a new sheet with =IF(ISBLANK('oldsheet'.A1);"";A1) in cell A1 and copy that formula so it repeats your existing sheet, and export that to csv.

edit flag offensive delete link more

Comments

OpenODS is not related to ODF .ods spreadsheet files.

The second hint to have a formula create an empty string and tick Quote all text cells in the export dialog works though.

erAck gravatar imageerAck ( 2020-07-20 11:45:30 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-07-18 21:54:35 +0200

Seen: 50 times

Last updated: Jul 19 '20