I’ve been importing data from a standard csv into an Oracle 12g db using [ Tools > Import Data ].
I’m finding that though the import goes fine, the strings in my text fields are surrounded by single quotes.
Where I simply want the text, [ I like cats ], I’m getting [ ‘I like cats’ ]. Additionally, empty text fields that should simply be set as NULL are ending up as [ ” ] (two single quotes).
Is there a preference somewhere that I need to set or unset to fix this?
csv example
Response
Sachin Prakash over 5 years ago
Hi,
In the Import Wizard > General tab, ensure that you’ve chosen the correct value for “String Quoted Identifier”. The problem you’re describing seems to be related to not having chosen the correct String Quoted Identifier. In the Import Wizard > General tab, take a look at the “Sample File Columns” section. If this section is showing quotes, then that means that there is a mismatch between the String Quoted Identifier that you have chosen vs the contents of the file itself.
Based upon your csv example, I chose String Quoted Identifier as ” (double quotes) and the below insert statements were generated:
INSERT INTO import_test(col1, col2, col3, col4, col5)
VALUES(‘1’, ‘7’, ‘In-Class examples that we had to do on the board.’, ‘-2’, ‘7ad6cf24-a553-491f-96e9-21866c163bbc’)
GO
INSERT INTO import_test(col1, col2, col3, col4, col5)
VALUES(‘1′, ’11’, ”, ‘3’, ‘7ad6cf24-a553-491f-96e9-21866c163bbc’)
GO
Additionally, empty text fields that should simply be set as NULL are ending up as [ ” ] (two single quotes).
See the above INSERT INTO example. Empty string fields are generated using ” (two single quotes). In order to generate a NULL, ADS requires that the cell value contain (null) and that you have enabled in Import Data > Options > Data Options > Set value to NULL on (null) text
Modified CSV example for null:
“1”,”7″,”In-Class examples that we had to do on the board.”,”-2″,”7ad6cf24-a553-491f-96e9-21866c163bbc”
“1”,”11″,”(null)”,”3″,”7ad6cf24-a553-491f-96e9-21866c163bbc”
After a little analysis. I discovered that the import wizard did what I wanted for other tables being imported. When looking at these files, I noticed that in order for the wizard to actually insert a null value into a field, the csv file would have to have an empty …. string? … between commas (field boundaries) NOT a set of double quotes.
So, to recap. Wanted nulls for empty fields from imported csv. Was getting fields with 2 single quotes { ,”, } after import. Discovered the csv import file should have { ,, } for empty csv fields, NOT { ,””, } to get the nulls I desired.
Thank you.