Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Oct 2014
    Posts
    21

    Unanswered: IMPORT .csv to db2

    Hello everyone,

    I'm new to this forum, and a "newbie" to DB2. I'm having difficulties with DB2, databases are something very unfamillliar to me

    My problem:

    I have a .csv file, created from MS Excel data. Now I want this .csv to import into DB2 table, but don't know how.

    I allready installed DB2 Express-C version 10.5 and Data studio 4.1.1 Client, and created my database, and now I'm stuck. I don't know how to create table for .csv and import it into DB2.

    Data in my .csv is (from left to right) :

    _Date,ID_Number,_Name,_Job_Title,_Start_Time,_End_ Time

    Please, If someone would be so kind to instruct me "step by step", I need this data for some other application !


    Thanks for help in advance !!
    Last edited by Lukael; 10-01-14 at 11:56.

  2. #2
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    I believe the IMPORT command will do it:
    http://www-01.ibm.com/support/knowle...0%2F3-6-2-4-58

    In the DB2 Control Center has an IMPORT option in the GUI as well, if you have that available.
    It will be something like this, but you'll have to check the exact options/syntax:
    db2 import from myfile.csv of del insert into mytable

    It will likely be a lot easier if the target table has the same column order as the CSV. Might be easiest to pull it into a temp table first, massage the data in there, then insert into your real table..

  3. #3
    Join Date
    Oct 2014
    Posts
    21
    Hi craigmc, thanks for response.

    In the DB2 Control Center has an IMPORT option in the GUI as well, if you have that available.
    I think I don't have Control Center in DB2, my version is 10.5, and can't find that anywhere. Also noticed on IBM site that Its no longer available, just Data Studio from now on, and I don't know where to find that in there.

    It will likely be a lot easier if the target table has the same column order as the CSV. Might be easiest to pull it into a temp table first, massage the data in there, then insert into your real table
    I told I'm a newbie. You probably think that I first need to create a table with SQL statement that is same as .csv file ? And why do I need a temp table, can't I create just one table ?

  4. #4
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    Often I'll get data I need to import which doesn't match exactly the structure of the table I need to get the data into. For example, the target table could have a primary key column which is not present in the CSV, or there could be TIMESTAMP columns in the table which show the create date for the record, or last update date, etc, that are not in the CSV.

    So you only need one table. I was just adding the additional hint that if your final target table does not match the CSV structure exactly (often the case), you could possibly save yourself some headaches by creating a "temp" table that does and get the data into it first because the IMPORT command would be more straightforward. Then use a SQL statement to get the data from the temp table to the final target table (a simple insert statement).

    Additionally, many DBAs would probably not trust the import to get everything correct (data conversion issues, etc), and would want to stage the data like I've mentioned before putting it into some production DB table so they can check over it and "massage" it if necessary.

  5. #5
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    So roughly step by step (my syntax here might not be 100% correct).
    - create your table.
    CREATE TABLE myschema.mytable (
    Date TIMESTAMP,
    ID_Number INT ,
    Name nvarchar(64),
    Job_Title nvarchar(64),
    Start_Time TIMESTAMP,
    End_Time TIMESTAMP )

    - load the data (from the db2cmd command line )
    db2 import from myfile.csv of del insert into myschema.mytable

    Again, I'm not 100% on the IMPORT command syntax, but it's something close to that.

  6. #6
    Join Date
    Oct 2014
    Posts
    21
    Ok,

    I will study syntax first and then try what you suggested, probably It will work. One more question ( I guess another stupid):

    - this has to be made every time .csv is imported into DB2, or DB2 then makes a "link" to file permanently ?

    I'll let you know about my accomplishments, thanks for helping me !!

  7. #7
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    It's a one time thing, you'll have to run the IMPORT command every time you want to pull data in. The table will be permanent, unless you drop it..

  8. #8
    Join Date
    Oct 2014
    Posts
    21
    I tried now... I created table with no problem, syntax was correct, but importing .csv from cmd didn't work.

    I get an error : " The user does not have the authority tu run the specified import command on table".

    I used:

    - db2 import from C:\U-sers\Luka\Documents\auth.csv of del insert into db2admin.razpored


    I think problem is administrator privileges. When I installed DB2 on my PC, another user account was created. I tried in that user account too, and something is getting performed with same syntax, but then It doesn't insert rows into tables.

    Take a look at this .txt result from db2cmd !
    Attached Files Attached Files
    Last edited by Lukael; 10-03-14 at 14:47.

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Lukael View Post
    What's wrong ??
    I think the error message is pretty clear: the date and time formats are wrong.
    ---
    "It does not work" is not a valid problem statement.

  10. #10
    Join Date
    Oct 2014
    Posts
    21
    Hi n_i,

    thanks for response. Yes, I see something is wrong with dates, but NONE of data is being copied.

    See this .txt, It's my .csv data. Can't see what is wrong in there

    Maybe I have to re-create these columns of TIMESTAMPS in some other way ??
    Attached Files Attached Files
    Last edited by Lukael; 10-03-14 at 15:29.

  11. #11
    Join Date
    Oct 2014
    Posts
    21
    I finally managed to import

    There were several issues:

    1. Syntax for creating table was wrong. Correct is :

    CREATE TABLE Mytable (
    Date DATE,
    ID_Number INT ,
    Name nvarchar(64),
    Job_Title nvarchar(64),
    Start_Time TIME,
    End_Time TIME)

    2. I had to change Date format in .CSV as "2014-01-01" instead of "01.01.2014"

    3. I had to change macro in Excel that creates .CSV files without double quotes (don't know why that didn't work, SQL Server doesn't have problem with this).

    And then an import command, which has to be run within DB2 administrator user account :

    db2 import from Myfile.csv of del insert into Myschema.Mytable

    Another option I found on the net is TxtToDB2 software, which can do Import of .txt and .csv automatically.

    Thanks guys for all help, both of you !!

  12. #12
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    try this:

    import from csv_sample.txt of del modified by dateformat="DD.MM.YYYY"
    insert into mytable
    Regards,
    Mark.

  13. #13
    Join Date
    Oct 2014
    Posts
    21
    Hi mark,

    I tried but got an error :

    SQL3192N In the filetmod a user specified format "DATEFORMAT" beginning with the string "DD" is not valid.
    My db2cmd code syntax was:

    Code:
    db2 import from C:\Users\db2admin.Luka-PC\Documents\auth.csv of del modified by dateformat="DD.MM.YYYY" insert into db2admin.razpored

  14. #14
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi Lukael,

    You have to escape double quotes if you run this command from the OS command prompt.
    Try this:
    Code:
    db2 import from C:\Users\db2admin.Luka-PC\Documents\auth.csv of del modified by dateformat=\"DD.MM.YYYY\" insert into db2admin.razpored
    Regards,
    Mark.

  15. #15
    Join Date
    Oct 2014
    Posts
    21
    Hi mark.b,

    yes now It works, but in DB2 dates in table are still in same format (YYYY-MM-DD).

    Thanks for your reply, but It really doesn't matter because I can change saving .csv in this YYYY-MM-DD format, so import command works same as here, without "modified" command.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •