Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Column names with spaces in sqlite
4,531 views
Skip to first unread message
sd
Dec 5, 2011, 2:19:40 AM12/5/11
to
I'm writing a script that opens Excel files and does some formating of
the content, but not to the headers. Therefore, I would like to be
able to create column headers in sqlite with spaces in them. Such as
"First Name" and also have column headers with characters such as / \
# & etc.
I've tried but keep getting all sorts of errors. Has anyone had to
deal with this? Thanks!
the content, but not to the headers. Therefore, I would like to be
able to create column headers in sqlite with spaces in them. Such as
"First Name" and also have column headers with characters such as / \
# & etc.
I've tried but keep getting all sorts of errors. Has anyone had to
deal with this? Thanks!
sd
Dec 5, 2011, 2:20:24 AM12/5/11
to
Jeff Godfrey
Dec 5, 2011, 9:40:33 AM12/5/11
to
On 12/5/2011 1:19 AM, sd wrote:
> I'm writing a script that opens Excel files and does some formating of
> the content, but not to the headers. Therefore, I would like to be
> able to create column headers in sqlite with spaces in them. Such as
> "First Name" and also have column headers with characters such as / \
> #& etc.
> I'm writing a script that opens Excel files and does some formating of
> the content, but not to the headers. Therefore, I would like to be
> able to create column headers in sqlite with spaces in them. Such as
> "First Name" and also have column headers with characters such as / \
>
> I've tried but keep getting all sorts of errors. Has anyone had to
> deal with this? Thanks!
For column names with spaces, I think surrounding the name with
> I've tried but keep getting all sorts of errors. Has anyone had to
> deal with this? Thanks!
double-quotes should work ("Column Name"). I'm not sure about the other
chars you mention.
That said, I wouldn't recommend using spaces (or special chars) as
you'll constantly be forced to deal with them in your code. Can you not
use an underscore char instead?
In my SQLite based apps, I always create simple table and column names
(no spaces or special chars), but also allow the User to create table
name aliases and column name aliases. So, if the end User doesn't like
seeing my raw table or column name in the app's table viewer, they can
define individual aliases as needed. If an alias exists for a table or
a column, it's presented in the GUI instead of the underlying name.
That way, my code isn't forced to deal with any special names, and yet
the User can create friendly names as they see fit. Seems to work well,
though it takes a bit of work to set up.
Jeff
Harald Oehlmann
Dec 5, 2011, 11:39:26 AM12/5/11
to
Within SQL, I would put the column names in "[]". this is also
helpful, if a column is named as a reserved word:
select [select],[col 2] form [table];
-Harald
helpful, if a column is named as a reserved word:
select [select],[col 2] form [table];
-Harald
sd
Dec 6, 2011, 9:23:15 AM12/6/11
to
Thanks both for your reponses. I'm probably better off forcing users
to not use spaces or special characters as column headers so I don't
have to worry about them down the road.
One other thing I noticed is that when my program picks up a cell
value with a leading zero from an Excel file (with TCOM), it is
treated as an octal and automatically converted to decimal when being
inserted into sqlite cell. That value "0641245055" is just a text and
not meant to be converted to anything else. I'm surprised this
happens only when I pick up cell values from an Excel file (using
TCOM) and not when I pick up values from a *.txt file! Any fool proof
way I can avoid uninteded coversions?
Thanks.
to not use spaces or special characters as column headers so I don't
have to worry about them down the road.
One other thing I noticed is that when my program picks up a cell
value with a leading zero from an Excel file (with TCOM), it is
treated as an octal and automatically converted to decimal when being
inserted into sqlite cell. That value "0641245055" is just a text and
not meant to be converted to anything else. I'm surprised this
happens only when I pick up cell values from an Excel file (using
TCOM) and not when I pick up values from a *.txt file! Any fool proof
way I can avoid uninteded coversions?
Thanks.
Harald Oehlmann
Dec 7, 2011, 3:01:12 AM12/7/11
to
On 6 Dez., 15:23, sd <sham...@hotmail.com> wrote:
> One other thing I noticed is that when my program picks up a cell
> value with a leading zero from an Excel file (with TCOM), it is
> treated as an octal and automatically converted to decimal when being
> inserted into sqlite cell. That value "0641245055" is just a text and
> not meant to be converted to anything else. I'm surprised this
> happens only when I pick up cell values from an Excel file (using
> TCOM) and not when I pick up values from a *.txt file! Any fool proof
> way I can avoid uninteded coversions?
SQLite uses the inner representation to detect the type.
> One other thing I noticed is that when my program picks up a cell
> value with a leading zero from an Excel file (with TCOM), it is
> treated as an octal and automatically converted to decimal when being
> inserted into sqlite cell. That value "0641245055" is just a text and
> not meant to be converted to anything else. I'm surprised this
> happens only when I pick up cell values from an Excel file (using
> TCOM) and not when I pick up values from a *.txt file! Any fool proof
> way I can avoid uninteded coversions?
This might be helpful or missleading.
To force the inner representation to be a string, use a string
operation on the value.
I did not try one of the following but they might help:
set v [format %s $v]
append v ""
To find out the current inner representation, you might use TCL 8.6:
tcl::unsupported::representation $v
-Harald
escargo
Dec 7, 2011, 11:16:15 AM12/7/11
to
On Dec 7, 2:01 am, Harald Oehlmann <wortka...@yahoo.de> wrote:
> On 6 Dez., 15:23, sd <sham...@hotmail.com> wrote:
>
> SQLite uses the inner representation to detect the type.
> This might be helpful or missleading.
> To force the inner representation to be a string, use a string
> operation on the value.
> I did not try one of the following but they might help:
> set v [format %s $v]
> append v ""
I ran into a problem with an Excel spread sheet where one of the
> On 6 Dez., 15:23, sd <sham...@hotmail.com> wrote:
>
> SQLite uses the inner representation to detect the type.
> This might be helpful or missleading.
> To force the inner representation to be a string, use a string
> operation on the value.
> I did not try one of the following but they might help:
> set v [format %s $v]
> append v ""
columns was for ZIP codes.
Excel did not want to retain leading 0s.
I had to tell it to format the cell as a string, rather than as a
number.
Similar data could have related problems in other contexts.
Harald Oehlmann
Dec 8, 2011, 3:01:05 AM12/8/11
to
here.
Otherwise, SQLite would not convert it to octal.
- Harald
0 new messages