topic Re: Incomplete load of CSV files in QlikView

Incomplete load of CSV files

sangland2 — Wed, 25 Nov 2020 16:16:04 GMT

When loading a CSV file using:(txt, codepage is 1252, embedded labels, delimiter is ',', msq) the load stops before it reaches the end of the file. There is no error but not all the data is loaded. This is because text fields in the file have not been quoted, as they should, so the number of fields being loaded is different when it hits this line. An error should be thrown, surely?

Re: Incomplete load of CSV files

qlikviewwizard — Fri, 14 Sep 2018 01:31:25 GMT

Can you attach the sample file?

Re: Incomplete load of CSV files

shiveshsingh — Fri, 14 Sep 2018 06:22:07 GMT

This needs to be checked, can you share the sample file?

Re: Incomplete load of CSV files

boraste-sagar — Fri, 14 Sep 2018 06:39:14 GMT

Most likely cause is an unexpected character. This can happen when the file was created with utf-8 and in qlikview the character set Western European is choosen. Or vice versa. Try changing the character set used to read the data.

(txt, codepage is 1252, embedded label, delimiter is ',', msq);

(txt, utf8, embedded labels, delimiter is ',', msq);

Re: Incomplete load of CSV files

sangland2 — Fri, 14 Sep 2018 07:06:06 GMT

Hi Guys sorry for the delay, it is a large file that contains client data. I have scrambled the data without affecting the problem. The issue occurs after loading 34295 rows, but there are 37666 rows. It doesn't matter which field(s) you load. If you open the csv file in Excel and save it back to csv it will load fine. Probably a hidden character in the data?

CRMQuote:

Load

summary

FROM

[CRMQuote3.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

;

Re: Incomplete load of CSV files

sangland2 — Fri, 14 Sep 2018 07:22:05 GMT

Both methods have the same problem. But my issue is that QV fails to load all the data and does not warn you. I found this in testing but it could easily have gone unnoticed.

Re: Incomplete load of CSV files

Frank_Hartmann — Fri, 14 Sep 2018 07:50:03 GMT

I guess its because some of your fields contains values like "".

If you omit the msq then it works fine!

try like this:

LOAD

Rowno() as Row,

oid,

cost,

createDate,

createdBy,

customerOrderNumber,

discount,

expiryDate,

margin,

myAddress,

myAssignedUser,

myCRMOrganisation,

myCRMSOSalesOrder,

myGTSalesPerson,

myQuoteStage,

type,

quoteNumber,

summary,

total,

mySAModule,

myCRMContact

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',');

Re: Incomplete load of CSV files

sangland2 — Fri, 14 Sep 2018 08:35:19 GMT

Hi Frank how many rows do you get compared to rows on the file.?

Re: Incomplete load of CSV files

Frank_Hartmann — Fri, 14 Sep 2018 08:48:13 GMT

I get 37665 lines not including the header!

Re: Incomplete load of CSV files

sangland2 — Fri, 14 Sep 2018 11:22:36 GMT

Strange, I get 33957 this time. Re log file

Re: Incomplete load of CSV files

Frank_Hartmann — Fri, 14 Sep 2018 11:32:32 GMT

did you look in my first post?

please change this line in your script:

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

(txt, codepage is 1252, embedded labels, delimiter is ',')

Re: Incomplete load of CSV files

sangland2 — Fri, 14 Sep 2018 11:41:02 GMT

The customerOrderNomber column has a single " character in line 33446 and again in 37154. Rows between are not loaded.