Import from CSV into Linux Sqlitestudio 3.2.1 losing first char of first column.

[mattsmi]

Details

Import multi-row, multi-column data from CSV. The first column is missing the first character of the data.

Steps to reproduce

See above.

Operating system

Linux Mint 19.2.

SQLiteStudio version

3.2.1 Linux build.

[pawelsalawa]

What is encoding of your csv file and what encoding do you choose in Import Dialog?

[mattsmi]

Have been saving a LibreOffice Calc spreadsheet as CSV, UTF-8 encoding. Have checked on disk to make sure that the output is correct. Importing into Sqlitestudio as CSV (both Data Source Type and the extension on the input file), and with UTF-8 encoding. Have been doing this for years with your brilliant product.

[pawelsalawa]

Okay.

1. Is the first character of first column missing in all rows, or just in first?
2. If you - just for test - decrease number of rows or columns - is the issue still present?

[mattsmi]

All rows. It was 600 rows. I reduced to 150. Still missing first char from first column on all rows.

[pawelsalawa]

What kind of data is stored in this first column? A number?

[mattsmi]

Codes that are alphanumeric and usually 6 characters long (e.g. FIX123) or a little longer (ENSUS204).

[pawelsalawa]

Okay, one more thing. If you reduce number of columns, to for example 2 - does this still happen?

[mattsmi]

An example. Headings on one line, then one row of data. TypeIndex,BaseID,Date_this_year,DoW,Day,Month,Feast_en,Feast_la,Short_name_en,Short_name_la,Notes,Lit_rank,Rank,FeastType,DaysFromEaster,EarliestMonth,EarliestDay,LatestMonth,LatestDay,EP1_PC_Ant1,EP1_PC_Ant2,EP1_PC_Ant3,EP1_Reading,EP1_Responsory,EP1_AntM,EP1_Petitions,EP1_Concluding,Invitatory,Read_P_Ant1,Read_P_Ant2,Read_P_Ant3,MP_PC_Ant1,MP_PC_Ant2,MP_PC_Ant3,MP_Reading,MP_Responsory,MP_AntB,MP_Intercessions,MP_Concluding,PDD_Ant1,PDD_Ant2,PDD_Ant3,Terce_Ant1,Sext_Ant1,None_Ant1,Terce_Reading,Sext_Reading,None_Reading,Terce_Responsory,Sext_Responsory,None_Responsory,EP2_PC_Ant1,EP2_PC_Ant2,EP2_PC_Ant3,EP2_Reading,EP2_Responsory,EP2_AntM,EP2_Petitions,EP2_Concluding,EP1_Hymn,EP1_PC1,EP1_PC2,EP1_PC3,Read_Hymn,Read_P1,Read_P2,Read_P3,MP_Hymn,MP_PC1,MP_PC2,MP_PC3,PDD_Hymn,PDD_PC1,PDD_PC2,PDD_PC3,EP2_Hymn,EP2_PC1,EP2_PC2,EP2_PC3,R1YA,R1YB,R1YC,R2YA,R2YB,R2YC,RespPsYA,RespPsYB,RespPsYC,GospelA,GospelB,GospelC,GosAcclA,GosAcclB,GosAcclC,VestmentColour,CalType,PrintOnCal,Feast_eo,Short_name_eo,RuleStartDate,RuleEndDate,Feast_ga,Short_name_ga FIX360,,,,25,12,Nativity of the Lord,In Nativitate Domini,Nativity of the Lord,In Nativitate Domini,,2,Solemnity,,,,,,,Ant0066,Ant0067,Ant0068,,,Ant0069,,,Ant0070,Ant0071,Ant0072,Ant0073,Ant0074,Ant0075,Ant0076,,,Ant0077,,,,,,Ant0078,Ant0079,Ant0080,,,,,,,Ant0081,Ant0082,Ant0083,,,Ant0084,,,,,,,,,,,,,,,,,,,,,,,RCLECT0596,RCLECT0596,RCLECT0596,RCLECT0509,RCLECT0509,RCLECT0509,RCLECT1673,RCLECT1673,RCLECT1673,RCLECT0683,RCLECT0683,RCLECT0683,,,,White,BENEDICT,1,Kristnasko,Kristnasko,,,An Nollaig,An Nollaig

[pawelsalawa]

Okay, let me see.

[pawelsalawa]

The example you gave above worked fine for me. First column imported contains exactly "FIX360". Can you try to copy your example from here and paste in some text editor into *.csv file and import that file?

Maybe LibreOffice is doing some trick with saved files, which is not visible at first glance.

[mattsmi]

Yes. Still causing the issue with only two, four, and six columns. I'll try copying the text above back into a normal text editor and trying to import.

[mattsmi]

I still see the problem. (I had tried this earlier in an attempt to troubleshoot.

[pawelsalawa]

Are you using SQLiteStudio from your Linux distro packages, or binaries from sqlitestudio.pl?

[mattsmi]

Downloaded from your site.

[mattsmi]

It's not available in the Linux Mint store. Thus I'm using the 64-bit version.

[pawelsalawa]

Got it. What about your system locale settings? Is there a chance anything changed in this matter? What does the locale command print for you?

[mattsmi]

LANG=en_GB.UTF-8 LANGUAGE=en_GB LC_CTYPE="en_GB.UTF-8" LC_NUMERIC=en_AU.UTF-8 LC_TIME=en_GB.UTF-8 LC_COLLATE="en_GB.UTF-8" LC_MONETARY=en_AU.UTF-8 LC_MESSAGES="en_GB.UTF-8" LC_PAPER=en_AU.UTF-8 LC_NAME=en_AU.UTF-8 LC_ADDRESS=en_AU.UTF-8 LC_TELEPHONE=en_AU.UTF-8 LC_MEASUREMENT=en_AU.UTF-8 LC_IDENTIFICATION=en_AU.UTF-8 LC_ALL=

[pawelsalawa]

Okay. Then maybe it's not about importing, but displaying in the grid. Let's check this - after importing select any cell of first column and switch to Form View. Please verify there if the value is really missing the 1st char?

[mattsmi]

It's really missing. It does not show in the form view. If I export it, it has been removed from the data.

[pawelsalawa]

And you say you've been doing it many, many times before and it started causing trouble only now?

[mattsmi]

I have done it for years. But I haven't had to do it for perhaps over a year — at least since the latest SQlitestudio update. (My data and systems have been stable, and I'd been working on other things.) I've been using Linux Mint for about 8 years, then Ubuntu Desktop Linux before then for perhaps two years, then flavours of Windows before that. The only thing I can see that has changed is SQLitestudio.

[pawelsalawa]

I see. Let's try with prior versions of SQLiteStudio. You can find all of them here: https://sqlitestudio.pl/files/sqlitestudio3/complete/linux64/

[mattsmi]

I just resurrected an old version of 3.1.1. Imported and it all worked fine.

[pawelsalawa]

Interesting. I will assume this is system related issue (I'm working on Windows at the moment). I will keep this one for tracking and testing on Linux.

[mattsmi]

OK. Have a good day, and thank you for your help!

[rgbtxus]

I just started using SqliteStudio today. Thank you!!! I can confirm that this problem of losing the first character of the first field occurs in the latest OSX version also. A bit annoying, but I just added a dummy first column to my import data all set to 1 and then dropped the first column after the import. This work around while inelegant solved my problem.

[tuffnatty]

Could it be a duplicate of #3520, #3418, #3413, #3403 ?

[pawelsalawa]

Looks like they are at least related, but I'm not sure if they are all duplicates. We'll see when they are all worked on. Thanks for finding this out.

[kaptainkory]

Similar, if not exact, issues here on Linux Mint SQLiteStudio 3.2.1. Only with some data imports more than just the first character are AWOL.

[JuanLM1978]

Same problem here

[Kukulkano]

Same problem here with Version 3.2.1 (downloaded from project site). I work on Linux KDE Neon (64 bit, based on Ubuntu 18.04 LTS). First character in first column is always missing (utf8).

I fixed it temporarily in my CSV by manually adding a blank to the beginning of all lines...

[gchr]

Same issue here with v.3.2.1 on Ubuntu 18.04 csv with 3 rows,2 cols: test-import.csv.txt

After import, sqlite3 shows:

sqlite> select * from testimport;
TSSCORE-100|Create serverInfo end-point
TSSCORE-100|Create serverInfo end-point
TSSCORE-101|Create marketSchedule end-points

All 'TSSCORE' should be 'ETSSCORE'

The file has LF endings. If I save it as CRLF, the import is ok.

[peterguy]

I am experiencing the same behavior. I'm using SQLiteStudio 3.2.1 on MacOS 10.15.4. Text files are UTF-8, locale is en_US.UTF-8. Various delimiters (comma, pipe, tab) and values (numeric and alpha) in the first column. With and without double-quotes around the values. First line as column names or not. Found a workaround by changing EOL from newline to carriage return + newline.

[marcobra]

seems related to the different rows line break eol char of the importing csv file, with row terminated with: cr+lf (dos) the import goes well lf (unix) the import cut the first char of the rows a workaround is to convert csv to DOS format (cr+lf terminated rows) it can be done with an editor that support the conversion of eol format or with unix2dos command in a deb based sysop:

sudo apt install dos2unix

to convert

unix2dos filename.csv ...