Is there a general way to force certain data types when importing csv data to sqlite?
I have a CSV file with mixed data and I'm trying to import it to an existing SQLite database. I have two applications to manage databases (one within my GIS software and an open source one called DB Browser), but in both the imported table simply has all the fields set as the 'text' data type, even though some of them contain only numbers. Occasionally I've managed to import a DBF where the column header specifies the data type, but even then it casts everything as 'integer' when I really need it to be 'real' (because bizarrely SQL can't do maths with integers properly).
Given that the same has happened with both my applications, I'm presuming the problem lies with the SQL importing method itself. Is there anything I can do by way of preparing the CSV file, or within SQL to force the fields into the right data type?
sqlite import
edited Nov 14 '18 at 10:32
D.Mendes
11810
asked Nov 14 '18 at 10:29
IsaacsonIsaacson
966
|
show 1 more comment
I have a CSV file with mixed data and I'm trying to import it to an existing SQLite database. I have two applications to manage databases (one within my GIS software and an open source one called DB Browser), but in both the imported table simply has all the fields set as the 'text' data type, even though some of them contain only numbers. Occasionally I've managed to import a DBF where the column header specifies the data type, but even then it casts everything as 'integer' when I really need it to be 'real' (because bizarrely SQL can't do maths with integers properly).
Given that the same has happened with both my applications, I'm presuming the problem lies with the SQL importing method itself. Is there anything I can do by way of preparing the CSV file, or within SQL to force the fields into the right data type?
sqlite import
edited Nov 14 '18 at 10:32
D.Mendes
11810
asked Nov 14 '18 at 10:29
IsaacsonIsaacson
966
Create a table with the appropriate column names and affinities, and then import into it. The second case described in sqlite.org/cli.html#csv_import
– Shawn
Nov 14 '18 at 10:50
Thanks, but I don't have the sqlite command line tool specified in the tutorial you linked and the '.mode' and '.import' functions are not recognised by either of my sqlite applications. Is that the only way?
– Isaacson
Nov 14 '18 at 11:14
I think it's a better tool than some random third party app for this sort of thing.
– Shawn
Nov 14 '18 at 11:37
1
Anyways, another possibility is to import into a temp table, and thenINSERT INTO ... SELECT ...into the real table from it. Sqlite will convert text values to other types if possible to match the column affinities of the destination table.
– Shawn
Nov 14 '18 at 11:40
Yes, I've downloaded it but can't get it to open any of my databases '.open FILENAME' just gives a new line, but doesn't recognise any of the tables. There's no confirmation, no list, no file browser, no cut and paste (to make sure the address is right or avoid having to type long queries out. I'm not sure I'm seeing how it's "better" yet, but maybe in time...
– Isaacson
Nov 14 '18 at 11:42
|
show 1 more comment
I have a CSV file with mixed data and I'm trying to import it to an existing SQLite database. I have two applications to manage databases (one within my GIS software and an open source one called DB Browser), but in both the imported table simply has all the fields set as the 'text' data type, even though some of them contain only numbers. Occasionally I've managed to import a DBF where the column header specifies the data type, but even then it casts everything as 'integer' when I really need it to be 'real' (because bizarrely SQL can't do maths with integers properly).
Given that the same has happened with both my applications, I'm presuming the problem lies with the SQL importing method itself. Is there anything I can do by way of preparing the CSV file, or within SQL to force the fields into the right data type?
sqlite import
edited Nov 14 '18 at 10:32
D.Mendes
11810
asked Nov 14 '18 at 10:29
IsaacsonIsaacson
966
I have a CSV file with mixed data and I'm trying to import it to an existing SQLite database. I have two applications to manage databases (one within my GIS software and an open source one called DB Browser), but in both the imported table simply has all the fields set as the 'text' data type, even though some of them contain only numbers. Occasionally I've managed to import a DBF where the column header specifies the data type, but even then it casts everything as 'integer' when I really need it to be 'real' (because bizarrely SQL can't do maths with integers properly).
Given that the same has happened with both my applications, I'm presuming the problem lies with the SQL importing method itself. Is there anything I can do by way of preparing the CSV file, or within SQL to force the fields into the right data type?
sqlite import
sqlite import
edited Nov 14 '18 at 10:32
D.Mendes
11810
asked Nov 14 '18 at 10:29
IsaacsonIsaacson
966
edited Nov 14 '18 at 10:32
D.Mendes
11810
asked Nov 14 '18 at 10:29
IsaacsonIsaacson
966
edited Nov 14 '18 at 10:32
D.Mendes
11810
edited Nov 14 '18 at 10:32
D.Mendes
11810
edited Nov 14 '18 at 10:32
D.Mendes
11810
11810
asked Nov 14 '18 at 10:29
IsaacsonIsaacson
966
asked Nov 14 '18 at 10:29
IsaacsonIsaacson
966
asked Nov 14 '18 at 10:29
IsaacsonIsaacson
966
966
Create a table with the appropriate column names and affinities, and then import into it. The second case described in sqlite.org/cli.html#csv_import
– Shawn
Nov 14 '18 at 10:50
Thanks, but I don't have the sqlite command line tool specified in the tutorial you linked and the '.mode' and '.import' functions are not recognised by either of my sqlite applications. Is that the only way?
– Isaacson
Nov 14 '18 at 11:14
I think it's a better tool than some random third party app for this sort of thing.
– Shawn
Nov 14 '18 at 11:37
1
Anyways, another possibility is to import into a temp table, and thenINSERT INTO ... SELECT ...into the real table from it. Sqlite will convert text values to other types if possible to match the column affinities of the destination table.
– Shawn
Nov 14 '18 at 11:40
Yes, I've downloaded it but can't get it to open any of my databases '.open FILENAME' just gives a new line, but doesn't recognise any of the tables. There's no confirmation, no list, no file browser, no cut and paste (to make sure the address is right or avoid having to type long queries out. I'm not sure I'm seeing how it's "better" yet, but maybe in time...
– Isaacson
Nov 14 '18 at 11:42
|
show 1 more comment
Create a table with the appropriate column names and affinities, and then import into it. The second case described in sqlite.org/cli.html#csv_import
– Shawn
Nov 14 '18 at 10:50
Thanks, but I don't have the sqlite command line tool specified in the tutorial you linked and the '.mode' and '.import' functions are not recognised by either of my sqlite applications. Is that the only way?
– Isaacson
Nov 14 '18 at 11:14
I think it's a better tool than some random third party app for this sort of thing.
– Shawn
Nov 14 '18 at 11:37
1
Anyways, another possibility is to import into a temp table, and thenINSERT INTO ... SELECT ...into the real table from it. Sqlite will convert text values to other types if possible to match the column affinities of the destination table.
– Shawn
Nov 14 '18 at 11:40
Yes, I've downloaded it but can't get it to open any of my databases '.open FILENAME' just gives a new line, but doesn't recognise any of the tables. There's no confirmation, no list, no file browser, no cut and paste (to make sure the address is right or avoid having to type long queries out. I'm not sure I'm seeing how it's "better" yet, but maybe in time...
– Isaacson
Nov 14 '18 at 11:42
Create a table with the appropriate column names and affinities, and then import into it. The second case described in sqlite.org/cli.html#csv_import
– Shawn
Nov 14 '18 at 10:50
Create a table with the appropriate column names and affinities, and then import into it. The second case described in sqlite.org/cli.html#csv_import
– Shawn
Nov 14 '18 at 10:50
Thanks, but I don't have the sqlite command line tool specified in the tutorial you linked and the '.mode' and '.import' functions are not recognised by either of my sqlite applications. Is that the only way?
– Isaacson
Nov 14 '18 at 11:14
Thanks, but I don't have the sqlite command line tool specified in the tutorial you linked and the '.mode' and '.import' functions are not recognised by either of my sqlite applications. Is that the only way?
– Isaacson
Nov 14 '18 at 11:14
I think it's a better tool than some random third party app for this sort of thing.
– Shawn
Nov 14 '18 at 11:37
I think it's a better tool than some random third party app for this sort of thing.
– Shawn
Nov 14 '18 at 11:37
1
1
Anyways, another possibility is to import into a temp table, and then
INSERT INTO ... SELECT ... into the real table from it. Sqlite will convert text values to other types if possible to match the column affinities of the destination table.– Shawn
Nov 14 '18 at 11:40
Anyways, another possibility is to import into a temp table, and then
INSERT INTO ... SELECT ... into the real table from it. Sqlite will convert text values to other types if possible to match the column affinities of the destination table.– Shawn
Nov 14 '18 at 11:40
Yes, I've downloaded it but can't get it to open any of my databases '.open FILENAME' just gives a new line, but doesn't recognise any of the tables. There's no confirmation, no list, no file browser, no cut and paste (to make sure the address is right or avoid having to type long queries out. I'm not sure I'm seeing how it's "better" yet, but maybe in time...
– Isaacson
Nov 14 '18 at 11:42
Yes, I've downloaded it but can't get it to open any of my databases '.open FILENAME' just gives a new line, but doesn't recognise any of the tables. There's no confirmation, no list, no file browser, no cut and paste (to make sure the address is right or avoid having to type long queries out. I'm not sure I'm seeing how it's "better" yet, but maybe in time...
– Isaacson
Nov 14 '18 at 11:42
|
show 1 more comment
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53298018%2fis-there-a-general-way-to-force-certain-data-types-when-importing-csv-data-to-sq%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53298018%2fis-there-a-general-way-to-force-certain-data-types-when-importing-csv-data-to-sq%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Create a table with the appropriate column names and affinities, and then import into it. The second case described in sqlite.org/cli.html#csv_import
– Shawn
Nov 14 '18 at 10:50
Thanks, but I don't have the sqlite command line tool specified in the tutorial you linked and the '.mode' and '.import' functions are not recognised by either of my sqlite applications. Is that the only way?
– Isaacson
Nov 14 '18 at 11:14
I think it's a better tool than some random third party app for this sort of thing.
– Shawn
Nov 14 '18 at 11:37
1
Anyways, another possibility is to import into a temp table, and then
INSERT INTO ... SELECT ...into the real table from it. Sqlite will convert text values to other types if possible to match the column affinities of the destination table.– Shawn
Nov 14 '18 at 11:40
Yes, I've downloaded it but can't get it to open any of my databases '.open FILENAME' just gives a new line, but doesn't recognise any of the tables. There's no confirmation, no list, no file browser, no cut and paste (to make sure the address is right or avoid having to type long queries out. I'm not sure I'm seeing how it's "better" yet, but maybe in time...
– Isaacson
Nov 14 '18 at 11:42