Tuesday, September 22, 2009

Direct Data load (Flat file to Table)

Direct loading of tables from flat file in SQL Assistant(queryman):

This is quite simple process. But I'm writing it down for the newbees :)
Step1: Create the table in database (it can also be an existing table, with data already in it)
Step2: The sequence of columns in table and flat file should match
Step3: Delimiter used in flat file is usually pipe "|"
Step4: Set queryman to run in Import mode (File -> Import Data)
Step5: Execute sql:
        INSERT INTO DBNAME.TBLNAME
        VALUES(?,?,?,?,?);
According to the number of columns in the table/file, the number of parameters (?) change.
Step6: The "Import file" dialog box will open. Choose the flat file that contains data to be loaded.
Step7: You are done! :-p

Note: Try not to use this method for large number of records. It will consume a lot of time and resources.
I suggest fastload/Multiload for such situations

13 comments:

  1. what is a delimiter, first of all?????? Can you emphasize on Step 3???

    ReplyDelete
  2. Hi Arun,

    delimiter is what you use to seperate each field. like a separator. for example, you might have come across a .csv file, which is comma separated. in teradata, default field separator is pipe (|)

    ReplyDelete
  3. Hi Meena,

    Thanks for your blog, I'm a newbie to TD. I followed your steps to load a flatfile with '|' delimiter into a TD existing/empty table using SQL Assistant.

    i have a prob
    flatfile:.csv
    JAN|FEB

    CREATE TABLE LOAD_TAB
    (COL1 CHAR(5),COL2 CHAR(5))

    INSERT INTO LOAD_TAB
    VALUES(?,?);

    Query contains 2 parameters but import file contains 1 data values

    ReplyDelete
  4. Hi Jampa,

    Perhaps it is too late a reply, but still, might help someone out there.
    This could happen when the default setting on your SQL Assistant is not the same as the delimiter you have on the flat file.
    To change the default settings, goto:
    Tools > Options > Export/Import tab > Use this delimiter between columns

    ReplyDelete
  5. Can you also please post the Fastload/Multiload script...that would be useful for many..

    Thanks in advance..

    ReplyDelete
  6. Hi Meena,
    Can u please post some example on bteq like how to insert the values from parameter file to table

    ReplyDelete
  7. Hi Anil & Juudu,
    I will create a new post for bteq/fastload/multiload examples. Thanks for the interest!

    ReplyDelete
  8. hi Meena, I have one scenario.
    I have one varchar field and I am importing records from the flat file.
    special char may also contain comma, pipe. So in this case how to differentiate delimiter with actual data.
    I tried with putting single quote. its still not working. Can you please help me.

    ReplyDelete
  9. Hi Kumar,
    You can choose carat delimiter, which has the least possibility of occurrence in varchar fields: ^

    ReplyDelete
  10. Hi Meena question my csv file contains spaces which delimiter do I choose for spaces?

    ReplyDelete
  11. Hi Dane,
    I am not sure what you mean by spaces - is it a single space character? or multiple?
    I would suggest not to use space as it might be present as a part of data itself, which could break the format of the file. For example, if your file has a char or varchar field, it can contain space.
    This is probably why TD does not have space as a delimiter option.
    If you are confident your data does not have spaces apart from delimiter, you could find and replace the spaces with TD allowed Delimiters such as: Tab , ; | # ~ ^

    ReplyDelete
  12. Hi Meena,
    I tried to insert data from flat file, but dates are not inserting i tried to cast the date but its not working. Any suggestions pls.....

    Thanks,
    Manju

    ReplyDelete