9 Replies Latest reply: Mar 30, 2012 10:29 AM by Dave Riley RSS

    Problems with loading a csv file

    Kinjal Shah

      Hi,

      I have a csv file that I am trying to load to my Qlikview document, however I have one of the text fields on the file that is messing up the data in the other fields. I am thinking it is seperating out the text in this one particular field if it sees a comma since I am using a .csv file. Is ther a way I can avoid this issue. Please help

        • Problems with loading a csv file

          Hi kinjal,
          Would using the text function in your load script maybe sort out your "problem field"?


          In your Load

           

          ...
          text(ProblemField) AS [WhateverYouWantToCallit],
          ...

           

          Hope this helps.

            • Re: Problems with loading a csv file
              sivaraj seeman

              Hi,

               

              Check if you any other common delimiter in csv file

               

              Regards,

              Sivaraj S

              • Problems with loading a csv file
                Kinjal Shah

                I tried this... Its not working . The problem is the other data fields are misaligned in the first step of the import itself.

                  • Re: Problems with loading a csv file
                    sivaraj seeman

                    can you attach sample csv file

                     

                    Sivaraj S

                      • Problems with loading a csv file
                        Kinjal Shah

                        I just found out that it is not the delimiter type that is an issue here. I changed the delimiter in the source file from ', to ~ and still have the same problem. The text in the field uses some kindof character that makes qlikview read it as the next field. I think it is the TAB in the text field that makes it considered as new field. Any solutions for that.

                          • Problems with loading a csv file
                            Dave Riley

                            Hi,

                             

                            This could be a carriage return character in one of the fields making Qlikview split the record into two or more lines, however when it reaches the end of the record it should see the start of the next record. 

                             

                            Is it just a handful of records being thrown out, or all of them?

                            Are you able to control the output of the csv file?

                             

                            You could try quoting the fields (if the source system allows this) so the data in the csv looks like ...

                             

                            "data1","data2","data3" etc - this may work as long as there are no " characters in the data!

                             

                            I have a text file feed with this problem on one record and it cannot be corrected in the source system so I have to feed my file through an external batch file process which replaces the CR character with an empty string, then loads as normal.  It's a nuisance but there's no other way to fix it unless you can correct it at source.

                             

                            flipside

                              • Problems with loading a csv file
                                Kinjal Shah

                                Hi Flipside, The problem is with a handful of records only. The fields are already quoted in my file. Would you be able to send me the script for the batch file that you run. I am really stuck beause of this issue. Is there a place we can report this problem to the Qlikview tech team.

                                  • Problems with loading a csv file
                                    Dave Riley

                                    This isn't a Qlikview issue, it's the data.  Anyway, here's the script I am using which seems to work.  Copy and paste this into a .vbs file. It won't run by itself as it needs parameters (see my notes later on).

                                     

                                     

                                    '================================

                                     

                                    Const ForReading = 1
                                    Const ForWriting = 2

                                    Dim sCurPath
                                    sCurPath = CreateObject("Scripting.FileSystemObject").GetAbsolutePathName("..\")

                                     

                                    strFileName = sCurPath & Wscript.Arguments(0)
                                    'msgbox strFileName

                                    strOldText = Replace(Wscript.Arguments(1),"vbLf",vbLf)
                                    'strOldText = "\" & vbLf
                                    strNewText = Wscript.Arguments(2)
                                    'strNewText = "\"

                                    'msgbox strFileName & " " &  strOldText  & " " & strNewText

                                    Set objFSO = CreateObject("Scripting.FileSystemObject")
                                    Set objFile = objFSO.OpenTextFile(strFileName, ForReading)

                                    strText = objFile.ReadAll
                                    objFile.Close
                                    strNewText = Replace(strText, strOldText, strNewText)

                                    Set objFile = objFSO.OpenTextFile(strFileName, ForWriting)
                                    objFile.WriteLine strNewText
                                    objFile.Close

                                     

                                    '===============================

                                     

                                     

                                    Then use something like this in your load script ...

                                     

                                    // Call the vbscript to remove linefeed characters in the datafeed file

                                    SET vbs = "..\?????.vbs"; // path to the vbs file

                                    SET LoadFile = "..\?????.csv"; //Path to file to cleanse (Filename cannot contain spaces.  )

                                    SET RemoveString = "\vbLf"; //vbLf will be replaced with correct vbLf character in vbs file

                                    SET NewString = "\";

                                     

                                    // Build command line and execute

                                    LET cmdExe = 'cmd.exe /c ' & chr(34) & '$(vbs)'  & chr(34) & ' ' & '$(LoadFile)'& ' ' & '$(RemoveString)' & ' ' & '$(NewString)';

                                    EXECUTE $(cmdExe);

                                     

                                     

                                    I had to pass "vbLf" as a text string in the command then change this to vbLf in the .vbs file.

                                     

                                    flipside

                        • Problems with loading a csv file
                          Dave Riley

                          Hi Kinjal,

                           

                          Unfortunately there isn't much you can do to prevent delimiter characters appearing in a field unless this can be controlled at the point of data entry.  What I have set up in some of my QV docs is a field count check, then flag as suspicious those records which appear to have additional fields (due to extra delimiters).

                           

                          First, get your top row (preferable the embedded field labels, or at least a record you know is correct) as a single text string and count the number of delimiter characters (pipe characters in this example) ...

                           

                          ExpFields:

                          FIRST 1

                          LOAD

                            substringcount ( @1, '|' ) AS ExpectedFields

                          FROM

                          [file]

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

                           

                          Then create a table of suspicious rows ...

                           

                          SuspectRows:

                          LOAD

                            RecNo() as SR_MainRowRef,

                            '  Record No: ' & RecNo() & ' has ' & substringcount (@1, '|' ) & ' fields, when ' & FieldValue('ExpectedFields',1) & ' expected.' as DQ_Comment

                          FROM

                          [file]

                          (txt, codepage is 1252, no labels, delimiter is \x7f, msq, header is 1 lines) 

                          where substringcount (@1, '|' ) <> FieldValue('ExpectedFields',1);

                          // delimiter here is a DEL character which is very unlikely to appear

                           

                          Then load your data as normal ensuring you include the following field ...

                           

                          , RecNo() as MainRowRef

                           

                          Finally, join the suspicious row data as follows ...

                           

                          left join (data)

                          LOAD

                          SR_MainRowRef as MainRowRef,

                          DQ_Comment,

                          1 as DQ_Flag

                          resident SuspectRows;

                           

                          .. and drop the SuspectRows table.

                           

                          Hope this helps

                           

                           

                          flipside