topic Re: Load a CSV file in QlikView

Load a CSV file

Sat, 22 Feb 2014 11:23:23 GMT

I have this troublesome table which is exported to a .csv file from a very old system.

To make sense out of the data I need to use this "fixed record" option in the wizard. So I make a lot of fixed fields, which the system auto name wiht a "@xx..." syntax. Afterwards I press finish in the wizard, and it the loads the script where I get all these Load "@xx, @xy, ...", if I do a reload or try to do something else, making a table from the fields, it does not work, and there is no table in the table viewer.

Any ideas?

Some of data is just vertical lines, which I of course intent to delete!

Re: Load a CSV file

MK_QSL — Sat, 22 Feb 2014 11:25:08 GMT

You can use Enable Transformation Step to load data

Table Files >> Select File >> Next >> Click on Enable Transformation Step...

Re: Load a CSV file

Sat, 22 Feb 2014 11:38:27 GMT

Thx, I did try that:

1. how can I change/define the field names from there?

2. I still get an error:

"Cannot open file 'C:\Users\...\....\Desktop\....csv' File not found (red. my translation) .

LOAD @1:1,

@2:11,

@12:12,

@13:37,

@38:38,

@39:54,

@55:55,

@56:68,

@69:69,

@70:82,

@83:85,

@86:98,

@99:100,

@101:113,

@114:114,

@115:128,

@129:130,

@131:n

FROM

[..\..\....csv]

(fix, codepage is 850, filters(

Remove(Col, Pos(Top, 3)),

Remove(Col, Pos(Top, 1)),

Remove(Col, Pos(Top, 3)),

Remove(Col, Pos(Top, 4)),

Remove(Col, Pos(Top, 5)),

Remove(Col, Pos(Top, 6)),

Remove(Col, Pos(Top, 7)),

Remove(Col, Pos(Top, 8)),

Remove(Col, Pos(Top, 9)),

Remove(Row, Pos(Top, 1)),

Remove(Row, Pos(Top, 3)),

Remove(Row, Pos(Top, 5)),

Remove(Row, Pos(Top, 3)),

Remove(Row, Pos(Top, 2)),

Remove(Row, Pos(Top, 1))

))"

Re: Load a CSV file

Anonymous — Sat, 22 Feb 2014 12:01:20 GMT

LOAD Name,

Class,

RollNo

FROM

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

Please check this file LOAD data into QlikView

Click on table files button,see below screen shot:

Click transformation below:

Click finish now:

Re: Load a CSV file

Sat, 22 Feb 2014 12:22:12 GMT

Can u upload Data File Please So can Help more Clearly

Re: Load a CSV file

Sat, 22 Feb 2014 14:45:38 GMT

Thx but I did do all this and still it is a problem. In your screenshot number 4, I only have "Garbage" and "Fill", btw.

Can it be that I run OO and not excel, and that the CSV in someway get attached to be opened in these programs, and therefore it cannot open the file!?

I modified it a bit in OO and saved the file as an Excel file this worked fine. Still it is a little workaround.

Re: Load a CSV file

Sat, 22 Feb 2014 14:46:17 GMT

Sorry, data is confidential.

Re: Load a CSV file

Anonymous — Sat, 22 Feb 2014 14:57:48 GMT

Hi Mikael,

Try to upload scrambled data,here is link to how to do it:

Preparing examples for Upload - Reduction and Data Scrambling

Or modify csv file and upload

Re: Load a CSV file

Sat, 22 Feb 2014 18:15:40 GMT

You can upload Example Data / or Creating Example type of data Present in Data in your Client data

Re: Load a CSV file

rbecher — Sat, 22 Feb 2014 19:55:21 GMT

Please upload an example with test data (maybe manuelly created) with a few records..

Re: Re: Load a CSV file

Sat, 22 Feb 2014 23:30:49 GMT

Thx for the enthusiasm, I did create a simple dummy .csv (code dos 850) from OO. It is not the exact same issue, where the original file is from an old ERP system. Though I got the same problem, so perhaps it is an issue that QV dont like OO.

Re: Re: Re: Load a CSV file

rbecher — Sat, 22 Feb 2014 23:58:42 GMT

It's easy if it's only an empty row before the header. You can use "header is 1 lines":

LOAD ID, 
     number, 
     A, 
     B, 
     C, 
     sum
FROM
[Test dummydata.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);

- Ralf

Re: Load a CSV file

vadimtsushko — Sun, 23 Feb 2014 04:47:21 GMT

-Mikael- написал(а):

Thx, I did try that:

1. how can I change/define the field names from there?

Answer to your first question: Change your load statement to something like

LOAD @1:1 as Field1,

@2:11 as Field2,

......

@131:n as AnotherFeldName

FROM

Re: Load a CSV file

Sun, 23 Feb 2014 18:54:57 GMT

Thx, but I was looking for a way to name the fields within the wizard, so I dont need to rename them in the script.

Re: Load a CSV file

Sun, 23 Feb 2014 18:59:11 GMT

well, thx in this case it works well, from the real table I need to do fixed separator in OO, so the first line get mixed up anyway.

But that is a minor problem, can you load and reload my CSV file without any issues? Do you have Excel?