Loading Data with "Copy" Commond


#1

I have TEXT Files data in following Format

A1 A2, B, C,D,E,F,G,H,I,J,K,L,M,N,O,P1:P2, Q,R,S,T

How I can load Data using "Copy command in MapD Database Table, which is looking Data as following

Column 1- A1
Column 2- A2
Column 3 - C
Column 4 - H
Column 5 - M
Column 6 - P1
Column 7 - S


#2

Hi @sumit -

Could you be more specific? Is your data in one file, or one file for A1, A2…T?

In general, to load data, you can either use the Immerse editor to upload data, which will generate a table definition for you, or you need to do a create table statement before using the copy command.

Best,
Randy


#3

Hi @sumit,

I guess you are asking if you can load a subset of columns of a text file into a table, with different column’s delimiters.
The short answer is you can’t because the data on the text file and table MUST be the same and you can specify just a single delimiter.

You have to transform the file before (or during) the load the transformations are fairly simples so you can think about using simple cmd-line tools like AWK.

Assuming you haven’t any column into the fields containing commas this command would do the job

cat simple.txt | awk -F’,’ ‘{ print substr($1,index($1," “)+1)”,“substr($1,1,index($1,” “)-1)”,"$3","$8","$13",“substr($16,1,index($16,”:")-1)","$19 }’

you can use the stream insert command to comsume the result of the command

https://www.mapd.com/docs/latest/6_loading_data.html#streaminsert

so cat simple.txt | awk -F’,’ ‘{ print substr($1,index($1," “)+1)”,“substr($1,1,index($1,” “)-1)”,"$3","$8","$13",“substr($16,1,index($16,”:")-1)","$19 }’ | StreamInsert etc.etc.


#4

Hi @aznable

Thank you for your feedback and your understanding is absolutely correct about about my requirement.

yes, you are right we can achieve the same with awk command but was thinking if we can achieve with Loader.

However your point is well noted and will check awk or any other faster way to achieve the required file generation for “copy” command input.

thank you once again for sharing feedback.

Regards,
sumit