sheet_to_json, is my file incorrect or sheet_to_json does not work correctly? #2823

New Issue

LuckyOne3 · 2022-11-11T12:08:45Z

LuckyOne3 commented

2022-11-11 12:08:45 +00:00

version - "xlsx": "^0.18.5"
language - JS

let data = XLSX.utils.sheet_to_json(workbook.Sheets[workbook.SheetNames[0]], {
                blankrows: true,
                raw: false,
                rawNumbers: true,
});

365, 366, 367,368 - lines at excel are skipped at array of data

this is 364 - line at excel

{
  "Номер шипмента": "0016399125",
  "Номер заказа": "1009830201",
  "№ отгрузки": "8016910145",
  "Stage": "0001",
  "Дата заг (с)": "12/11/22",
  "План Врм (с)": "05:20:00",
  "Рекламные накладные": "This shipment requires protective bags for emitting gum products (0.52% emitting).",
  "Кол-во коробов": "221,000",
  "Целых пал": "0",
  "Пикинг пал": "5,000000",
  "Кол-во пал": "5",
  "Пикинг": "221",
  "Тип трака": "K9",
  "Тип груз-ка описание": "Insulated 32pal.",
  "Номер Тр.комп": "0050001964",
  "Трансп компания": "АлгаТранс ООО",
  "Номер трака": ".",
  "Название клиента": "\"Союз Святого Иоанна Воина\" ОООРЦ \"\tд.Бабаиха\t\tFM Sidorovo\tул. Заводская\tгород Ступино, поселок Октябрьский\tмедкнижка\t\t0010399581\t0010399575\t\"Союз Святого Иоанна Воина\" ООО\t3-й Сетуньский пр-д, д.10\tг.Москва\t\n0016398873\t1009821965\t8016893984\t0001\t12/11/2022\t05:30:00\t\t\t7150,000\t6\t30,000000\t36\t6304\tK9\tInsulated 32pal.\t0050001571\tВОЯЖ-ГРУПП ООО\t.\t\"Пет Ритейл\" ООО\tд.Крёкшино\tТерминальный пр-д, д.1, стр.6\tFM Sidorovo\tул. Заводская\tгород Ступино, поселок Октябрьский\t\t\t0010394144\t0010373679\t\"Пет Ритейл\" ООО\tВаршавское ш., д.42\tг.Москва\t\n0016399069\t1009025195\t8016909317\t0001\t12/11/2022\t05:40:00\t\t\t4665,000\t18\t8,000000\t26\t1120\tK9\tInsulated 32pal.\t0050003235\tМотус-Трак ООО\t.\tТорговый дом \"Перекресток\" АО(РЦ ЗТ\tг.Санкт-Петербург\tул.Седова, д.9, кор.4, лит. А\tFM Sidorovo\tул. Заводская\tгород Ступино, поселок Октябрьский\tМедкнижка\t\t0010335342\t0010445877\tПятерочка-Перекресток\tул.Средняя Калитниковская д.28,\tг.Москва\t\n0016398786\t1009624155\t8016885404\t0001\t12/11/2022\t08:10:00\t\t\t7062,000\t32\t1,000000\t33\t126\tK9\tInsulated 32pal.\t0050001472\tТК МОК АВТОТРАНС ООО\t.\tООО\"Сладкая жизнь Н.Н.(РЦ Кстово)",
  "Адрес/Город": "д.Опалиха",
  "Адрес доставки/Улица": "Кстовский Индустриальный парк",
  "склад отгрузки": "FM Sidorovo",
  "место отгрузки": "ул. Заводская",
  "Отгрузка/Улица": "город Ступино, поселок Октябрьский",
  "SAP код склада": "0010335326",
  "SAP код клиента": "0010335064",
  "Юр. Название клиента": "ООО \"Сладкая жизнь Н.Н.\"",
  "Юр. Адрес улица": "ул.С.Есенина, д.10",
  "Юр. Адрес город": "г.Нижний Новгород"
}

version - "xlsx": "^0.18.5" language - JS ``` let data = XLSX.utils.sheet_to_json(workbook.Sheets[workbook.SheetNames[0]], { blankrows: true, raw: false, rawNumbers: true, }); ``` 365, 366, 367,368 - lines at excel are skipped at array of data this is 364 - line at excel ``` { "Номер шипмента": "0016399125", "Номер заказа": "1009830201", "№ отгрузки": "8016910145", "Stage": "0001", "Дата заг (с)": "12/11/22", "План Врм (с)": "05:20:00", "Рекламные накладные": "This shipment requires protective bags for emitting gum products (0.52% emitting).", "Кол-во коробов": "221,000", "Целых пал": "0", "Пикинг пал": "5,000000", "Кол-во пал": "5", "Пикинг": "221", "Тип трака": "K9", "Тип груз-ка описание": "Insulated 32pal.", "Номер Тр.комп": "0050001964", "Трансп компания": "АлгаТранс ООО", "Номер трака": ".", "Название клиента": "\"Союз Святого Иоанна Воина\" ОООРЦ \"\tд.Бабаиха\t\tFM Sidorovo\tул. Заводская\tгород Ступино, поселок Октябрьский\tмедкнижка\t\t0010399581\t0010399575\t\"Союз Святого Иоанна Воина\" ООО\t3-й Сетуньский пр-д, д.10\tг.Москва\t\n0016398873\t1009821965\t8016893984\t0001\t12/11/2022\t05:30:00\t\t\t7150,000\t6\t30,000000\t36\t6304\tK9\tInsulated 32pal.\t0050001571\tВОЯЖ-ГРУПП ООО\t.\t\"Пет Ритейл\" ООО\tд.Крёкшино\tТерминальный пр-д, д.1, стр.6\tFM Sidorovo\tул. Заводская\tгород Ступино, поселок Октябрьский\t\t\t0010394144\t0010373679\t\"Пет Ритейл\" ООО\tВаршавское ш., д.42\tг.Москва\t\n0016399069\t1009025195\t8016909317\t0001\t12/11/2022\t05:40:00\t\t\t4665,000\t18\t8,000000\t26\t1120\tK9\tInsulated 32pal.\t0050003235\tМотус-Трак ООО\t.\tТорговый дом \"Перекресток\" АО(РЦ ЗТ\tг.Санкт-Петербург\tул.Седова, д.9, кор.4, лит. А\tFM Sidorovo\tул. Заводская\tгород Ступино, поселок Октябрьский\tМедкнижка\t\t0010335342\t0010445877\tПятерочка-Перекресток\tул.Средняя Калитниковская д.28,\tг.Москва\t\n0016398786\t1009624155\t8016885404\t0001\t12/11/2022\t08:10:00\t\t\t7062,000\t32\t1,000000\t33\t126\tK9\tInsulated 32pal.\t0050001472\tТК МОК АВТОТРАНС ООО\t.\tООО\"Сладкая жизнь Н.Н.(РЦ Кстово)", "Адрес/Город": "д.Опалиха", "Адрес доставки/Улица": "Кстовский Индустриальный парк", "склад отгрузки": "FM Sidorovo", "место отгрузки": "ул. Заводская", "Отгрузка/Улица": "город Ступино, поселок Октябрьский", "SAP код склада": "0010335326", "SAP код клиента": "0010335064", "Юр. Название клиента": "ООО \"Сладкая жизнь Н.Н.\"", "Юр. Адрес улица": "ул.С.Есенина, д.10", "Юр. Адрес город": "г.Нижний Новгород" } ```

RU32 PLAN 20221110.XLS

307 KiB

sheetjs commented

2022-11-11 21:18:15 +00:00

The attached file is a TSV. There are a few problems viz-a-vis our understanding of delimited files.

First: you should pass the option FS: "\t" to XLSX.read or XLSX.readFile. The reason is SheetJS assumes en-US semantics. Without a proper sep= pragma, TSV file separators are guessed by looking at the first KB of data.

Row 363 looks strange:

0016399124	1009794156	8016646591	0001	12/11/2022	05:10:00			6394,000	26	7,000000	33	1489	K9	Insulated 32pal.	0050001472	ТК МОК АВТОТРАНС ООО	.	"Союз Святого Иоанна Воина" ОООРЦ "	д.Бабаиха		FM Sidorovo	ул. Заводская	город Ступино, поселок Октябрьский	медкнижка		0010399581	0010399575	"Союз Святого Иоанна Воина" ООО	3-й Сетуньский пр-д, д.10	г.Москва

The problem is the quoting. The start of the run-on cell is

"Союз Святого Иоанна Воина" ОООРЦ "	д.Бабаиха

Here, the field starts with a double quote. The parser assumes that the quoting is in effect, and looks for an end quote to end the block. That would be the second pair, after Воина. It encounters the third and assumes that is part of a quoted string that terminates when the next double quote is found.

If you are generating the TSV files, keep in mind the correct Excel quoting rule is to use two pairs of double quotes. That is to say, the literal string

Союз Святого Иоанна Воина" ОООРЦ

should be written as

"Союз Святого Иоанна Воина"" ОООРЦ"

If you can share a screenshot of how that row looks in Excel, then we can go back and revisit quoting rules.

The attached file is a TSV. There are a few problems viz-a-vis our understanding of delimited files. First: you should pass the option `FS: "\t"` to `XLSX.read` or `XLSX.readFile`. The reason is SheetJS assumes en-US semantics. Without a proper `sep=` pragma, TSV file separators are guessed by looking at the first KB of data. Row 363 looks strange: ``` 0016399124 1009794156 8016646591 0001 12/11/2022 05:10:00 6394,000 26 7,000000 33 1489 K9 Insulated 32pal. 0050001472 ТК МОК АВТОТРАНС ООО . "Союз Святого Иоанна Воина" ОООРЦ " д.Бабаиха FM Sidorovo ул. Заводская город Ступино, поселок Октябрьский медкнижка 0010399581 0010399575 "Союз Святого Иоанна Воина" ООО 3-й Сетуньский пр-д, д.10 г.Москва ``` The problem is the quoting. The start of the run-on cell is ``` "Союз Святого Иоанна Воина" ОООРЦ " д.Бабаиха ``` Here, the field starts with a double quote. The parser assumes that the quoting is in effect, and looks for an end quote to end the block. That would be the second pair, after `Воина`. It encounters the third and assumes that is part of a quoted string that terminates when the next double quote is found. If you are generating the TSV files, keep in mind the correct Excel quoting rule is to use two pairs of double quotes. That is to say, the literal string ``` Союз Святого Иоанна Воина" ОООРЦ ``` should be written as ``` "Союз Святого Иоанна Воина"" ОООРЦ" ``` If you can share a screenshot of how that row looks in Excel, then we can go back and revisit quoting rules.

LuckyOne3 commented

2022-11-11 21:34:14 +00:00

Thank you for answering, on the screen shot is 363 line of excel and how "Союз Святого Иоанна Воина ОООРЦ" is look. I do not generate this files, i just need to parse them, but I was surprised to see such behavior.
As soon as possible i will try this file with XSLX.read option with feed back.

Thank you for answering, on the screen shot is 363 line of excel and how "Союз Святого Иоанна Воина ОООРЦ" is look. I do not generate this files, i just need to parse them, but I was surprised to see such behavior. As soon as possible i will try this file with XSLX.read option with feed back.

Снимок экрана 2022-11-12 в 00.23.26.png

1.4 MiB

Снимок экрана 2022-11-12 в 00.31.23.png

1.4 MiB

sheetjs commented

2022-11-11 21:43:02 +00:00

Thanks for sharing! This is surprising for us as well. We'll test some more cases to get a better understanding for how Excel works.

LuckyOne3 commented

2022-11-11 21:45:14 +00:00

     for (const file of files) {
            await writeFile(`${uploadFolder}/${file.originalname}`, file.buffer);

            let workbook = XLSX.readFile(`${uploadFolder}/${file.originalname}`, {FS: "\t"});
            let data = XLSX.utils.sheet_to_json(workbook.Sheets[workbook.SheetNames[0]], {
                raw: false
            });
     

            data.forEach((line: any, index) => {

                this.logger.log(line)

            });

my service is getting file from post query, save file to local folder, and parsing excel, maybe I can give you something more, but I do not know what to send you for this issue, сan you tell me?
I have been tried with new option, but it did not help for concating lines.

``` for (const file of files) { await writeFile(`${uploadFolder}/${file.originalname}`, file.buffer); let workbook = XLSX.readFile(`${uploadFolder}/${file.originalname}`, {FS: "\t"}); let data = XLSX.utils.sheet_to_json(workbook.Sheets[workbook.SheetNames[0]], { raw: false }); data.forEach((line: any, index) => { this.logger.log(line) }); ``` my service is getting file from post query, save file to local folder, and parsing excel, maybe I can give you something more, but I do not know what to send you for this issue, сan you tell me? I have been tried with new option, but it did not help for concating lines.

LuckyOne3 commented

2022-11-11 21:49:17 +00:00

line - 363

{
  "Номер шипмента": "0016399124",
  "Номер заказа": "1009794156",
  "№ отгрузки": "8016646591",
  "Stage": "0001",
  "Дата заг (с)": "12/11/22",
  "План Врм (с)": "05:10:00",
  "Кол-во коробов": "6394,000",
  "Целых пал": "26",
  "Пикинг пал": "7,000000",
  "Кол-во пал": "33",
  "Пикинг": "1489",
  "Тип трака": "K9",
  "Тип груз-ка описание": "Insulated 32pal.",
  "Номер Тр.комп": "0050001472",
  "Трансп компания": "ТК МОК АВТОТРАНС ООО",
  "Номер трака": ".",
  "Название клиента": "Союз Святого Иоанна Воина\" ОООРЦ \"\tд.Бабаиха\t\tFM Sidorovo\tул. Заводская\tгород Ступино, поселок Октябрьский\tмедкнижка\t\t0010399581\t0010399575\t\"Союз Святого Иоанна Воина\" ООО\t3-й Сетуньский пр-д, д.10\tг.Москва\t\n0016399125\t1009800324\t8016905434\t0001\t12/11/2022\t05:20:00\tThis shipment requires protective bags for emitting gum products (0.52% emitting).\t\t5182,000\t7\t21,000000\t28\t3982\tK9\tInsulated 32pal.\t0050001964\tАлгаТранс ООО\t.\t\"Союз Святого Иоанна Воина\" ОООРЦ ",
  "Адрес/Город": "д.Бабаиха",
  "склад отгрузки": "FM Sidorovo",
  "место отгрузки": "ул. Заводская",
  "Отгрузка/Улица": "город Ступино, поселок Октябрьский",
  " коментарии": "медкнижка",
  "SAP код склада": "0010399581",
  "SAP код клиента": "0010399575",
  "Юр. Название клиента": "\"Союз Святого Иоанна Воина\" ООО",
  "Юр. Адрес улица": "3-й Сетуньский пр-д, д.10",
  "Юр. Адрес город": "г.Москва"
}

I want to say - Thank you again, to be honest, I didn’t expect you to answer :)

line - 363 ``` { "Номер шипмента": "0016399124", "Номер заказа": "1009794156", "№ отгрузки": "8016646591", "Stage": "0001", "Дата заг (с)": "12/11/22", "План Врм (с)": "05:10:00", "Кол-во коробов": "6394,000", "Целых пал": "26", "Пикинг пал": "7,000000", "Кол-во пал": "33", "Пикинг": "1489", "Тип трака": "K9", "Тип груз-ка описание": "Insulated 32pal.", "Номер Тр.комп": "0050001472", "Трансп компания": "ТК МОК АВТОТРАНС ООО", "Номер трака": ".", "Название клиента": "Союз Святого Иоанна Воина\" ОООРЦ \"\tд.Бабаиха\t\tFM Sidorovo\tул. Заводская\tгород Ступино, поселок Октябрьский\tмедкнижка\t\t0010399581\t0010399575\t\"Союз Святого Иоанна Воина\" ООО\t3-й Сетуньский пр-д, д.10\tг.Москва\t\n0016399125\t1009800324\t8016905434\t0001\t12/11/2022\t05:20:00\tThis shipment requires protective bags for emitting gum products (0.52% emitting).\t\t5182,000\t7\t21,000000\t28\t3982\tK9\tInsulated 32pal.\t0050001964\tАлгаТранс ООО\t.\t\"Союз Святого Иоанна Воина\" ОООРЦ ", "Адрес/Город": "д.Бабаиха", "склад отгрузки": "FM Sidorovo", "место отгрузки": "ул. Заводская", "Отгрузка/Улица": "город Ступино, поселок Октябрьский", " коментарии": "медкнижка", "SAP код склада": "0010399581", "SAP код клиента": "0010399575", "Юр. Название клиента": "\"Союз Святого Иоанна Воина\" ООО", "Юр. Адрес улица": "3-й Сетуньский пр-д, д.10", "Юр. Адрес город": "г.Москва" } ``` I want to say - Thank you again, to be honest, I didn’t expect you to answer :)

LuckyOne3 commented

2022-11-11 21:59:27 +00:00

At this screen shot for column "Название клиента" - sentence Союз Святого Иоанна Воина\" ОООРЦ parsed ok.
But for the column "Юр. Название клиента" - sentence Союз Святого Иоанна Воина ООО - is parsed as \"Союз Святого Иоанна Воина\" ООО

At this screen shot for column `"Название клиента"` - sentence `Союз Святого Иоанна Воина\" ОООРЦ` parsed ok. But for the column `"Юр. Название клиента"` - sentence `Союз Святого Иоанна Воина ООО` - is parsed as `\"Союз Святого Иоанна Воина\" ООО`

Снимок экрана 2022-11-12 в 00.56.30.png

1.5 MiB