topic ODBC Connection to csv issue in QlikView

ODBC Connection to csv issue

Fernan — Wed, 04 Aug 2021 14:23:25 GMT

Hello, I am trying to make an Odbc connection to a series of * .csv files,
to load their content in a table, but I receive a series of errors,
I would appreciate any contribution to solve it. Attached the errors and part of the code,
Thank you very much in advance.

The code is this:

FOR EACH File in FileList('*.CSV');

ODBC CONNECT32 TO [Csv FilesOk;DBQ=$(File)];

tables:
SQLtables;

DISCONNECT;

Let count = SubStringCount(File,'\');
Let tablename = left(Subfield(File,'\',$(count)+1),len(Subfield(File,'\',$(count)+1))-5);

FOR i = 0 to NoOfRows('tables')-1

LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));

if(Exists(TableName($(sheetName)))) then

Concatenate
'$(tablename)':
Load *
From $(File)(ooxml, embedded labels, table is [$(sheetName)]);

ELSE

NoConcatenate
'$(tablename)':
Load *
From $(File)(ooxml, embedded labels, table is [$(sheetName)]);

ENDIF

NEXT i;

Drop table tables;

Next File;

Re: ODBC Connection to csv issue

Frank_Hartmann — Wed, 04 Aug 2021 19:24:26 GMT

Try this:

SET ErrorMode=0; FOR EACH File in FileList('*.csv'); ODBC CONNECT32 TO [Excel Files;DBQ=$(File)]; tables: SQLtables; DISCONNECT; Let count = SubStringCount(File,'\'); Let tablename = 'Table '& left(Subfield(File,'\',$(count)+1),len(Subfield(File,'\',$(count)+1))-4); FOR i = 0 to NoOfRows('tables')-1 LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36)); NoConcatenate '$(tablename)': Load * From $(File)(txt, codepage is 28591, embedded labels, delimiter is ';', msq); NEXT i; Drop table tables; Next File;

Re: ODBC Connection to csv issue

Fernan — Wed, 04 Aug 2021 20:11:55 GMT

Hi Frank, I have tried several .csv files but it keeps giving me an error.
I am attaching a screenshot and the message it gives me.

Thank you very much in advance.

Greetings

Connecting to Excel Files;DBQ=C:\Fenix\Modelo de Datos\KPIS\Nuevosdatos\tablita.csv;
Failed to connect
Connector connect error: SQL##f - SqlState: S1000, ErrorCode: 4294962281, ErrorMsg: [Microsoft][Controlador ODBC Excel] La tabla externa no tiene el formato esperado.
ODBC CONNECT32 TO ***