CSV date to TimeStampRSS

4 replies

Last post Feb 05, 2007 07:33 AM by Anonymous

  • CSV date to TimeStamp

    Feb 03, 2007 03:54 PM|Anonymous|LINK

    This is probably easy but given an input csv like

    REC_DATE,ACC,ORD_LOC,ORD_
    01302007,07-030-2368,ART,4028232

    I am using the COM interface to parse into an Access Table. I want REC_DATE to be a date filed in the table.  I tried setting

    oInput = CreateObject("MSUtil.LogQuery.CSVInputFormat")
    oInput.headerRow = 1
    oInput.iTsFormat = "MMddyyyy"

    and SQL like

    cSQL = "SELECT TO_TIMESTAMP( 'REC_DATE', 'MMddyyyy') AS RECDATE..."

    but the field data is entered as a LONG.

    Can this be done? Stan

     

     

     

     

     

     

    CSV and TSV input formats

  • Re: CSV date to TimeStamp

    Feb 03, 2007 08:51 PM|Anonymous|LINK

    I would expect the iTsFormat option to work for you, but maybe you could try setting DTlines to 0 to turn off automatic type parsing.

    CSV and TSV input formats

  • Re: CSV date to TimeStamp

    Feb 04, 2007 10:14 AM|Anonymous|LINK

    LogParser User : StanL

    Quote: I would expect the iTsFormat option to work for you, but maybe you could try setting DTlines to 0 to turn off automatic type parsing.

    No, it appears to have no effect. I did set oInput.dtLines=0 and then SQL such as

    SELECT TO_TIMESTAMP( REC_DATE, 'MMddyyyy') AS RECDATE gives a valid field. But that brought an even greater problem. The output specification I used (I am doing this to help someone on another scripting forum... trying to tell them how great LogParser is)

    oOut = CreateObject("MSUtil.LogQuery.SQLOutputFormat.1")

    oOut.oConnString = "Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=c:\temp;"

    errors if I add ACC AS ACCESSION to the SQL string. I get an ODBC error that the field ACCESSION exceeds the limits. The problem seems to be that LogParser defaults text to 255 chars (because this will work if I specify an Access Table), and dBase is limited to 250. Would be nice if LogParser supported OLEDB Providers, or allowed a field length in the output field for SQL.

    I have suggested to the person on the other forum that probably he will need to use Jet 4.0, ADOX and schema.ini to parse the text file as needed. However, if there are hidden work-arounds in LogParser I'm all ears

    CSV and TSV input formats

  • Re: CSV date to TimeStamp

    Feb 04, 2007 10:21 PM|Anonymous|LINK

    You could try setting the maxStrFieldLen on your SQL output format.

    CSV and TSV input formats

  • Re: CSV date to TimeStamp

    Feb 05, 2007 07:33 AM|Anonymous|LINK

    LogParser User : StanL

    Quote: You could try setting the maxStrFieldLen on your SQL output format.

    Bingo! Thank you.

    CSV and TSV input formats