If you have a URL path to a CSV data source, you can use this to load data into InMemory.
In this example I will try to load data from this URL-located CSV file:
https://raw.githubusercontent.com/jasperdebie/VisInfo/master/us-state-capitals.csv
It contains a list of US capitals together their State name and Longitude/Latitude information.
- In your InMemory Project, add a new data source:
- Select the CSV data source type and make any necessary changes:
In my example, I changed the Name, Load as string and Has headers settings. - Click 'Finish' and add an Import task to your project (together with a Save task):
Notice that I simply pasted the URL in between the brackets in the 'SELECT * FROM [ ]' query. - Run the InMemory Project to load the CSV data into the database.
- To verify that data has been loaded, open the TARGIT InMemory Query Tool, connect to the server and select the fields for the imported table:
Comments
Hi Ole,
Thank you for the guide. Is it possible to define different datatypes for columns? I have a csv-file where some columns is strings and other numeric. One of the string columns has a value '08' and if i do not tick "load as string" the 0 is removed and 8 is loaded in stead. On the other hand if I tick load as string the numeric columns is loaded as strings.
I have made a workaround where I load the file to a "Staging Table" and then convert the columns to the desired datatype. However it would be nice if it was possible to do this in one step. In SSIS it is possible to define data types per column.
Hi Allan.
It is actually possible.
Consider this example, where I load five columns. The "Test" column has your exact problem in it. The zero is removed since it is loaded as INT64.
But you can define the metadata when you load the data.
SELECT Dato,
test,
Saldo,
Kommentar,
Nummer
FROM [C:\transactions\csv\test.csv](METADATA: test(type:string, ordinal:2))
In the METADATA statement, the "test" is the name of the column. The "ordinal is the logical column no. In my example I have ordinal:2 since the "test" column is the second one.
Please note that the space right after "METADATA:" should be very important.
Give it a shot :-)
Hi Allan,
Thank you for your answer and suggestion. I just tested it out and unfortunately there no difference - the zero is still removed from the column "Lagersted"
Datasource is set up like this
And the query and preview looks like this.
Hi Allan.
Your data source and code looks correct, so strange. Are you willing to share your file or parts of it?
Hi again,
Yes, I can share the file. However it's not possible to attach it here. I uploaded it to Mega som it can be downloaded here
https://mega.nz/file/jXpCybBY#RlgxUUHJTrpeXRRQ1U0TVGsgn0V5OXKg3Vfa1jlKqwo
Thank you for the file.
I had to change two things to make it work.
1. The "ordinal" must apparently be zero indexed, so when you have column 2, just write "ordinal:1" I was not aware of that.
2. Special characters is always an issue. It can probably be handled, but when I change "Størrelse" to "Storrelse", the file can be loaded correctly with Lagersted as string.
Please try this and let me know it works.
And if the file is auto generated, so you have to handle the "ø", let me know :-)
The file is autogenerated by our ERP-system, so I'm not able to change ø to o without doing it manually every time :)
Thought so :-) Let me find out how it is done.
Why not just load * like this:
The only dependency is hereby that "Lagersted" has to be the second column in the file.
That worked for me.
Hi again,
I have now had time to test it, and it works with select * from... and using ordinal 1 instead of 2. However I also have to make a product id that is a combination of 3 columns ( Varenummer+'-'+Farve+'-'+Størrelse), so I have to use a staging table anyway. Not able to make it work directly from the csv-file.
Hi,
Just wanted to share the syntax if you need to set METADATA for more than one column. The syntax is as below.
SELECT * FROM [C:\test.csv] (METADATA: [Column1](type:string, ordinal:0), [Column2](type:string, ordinal:1), [Column3](type:string, ordinal:2))
/Allan
Thank you for sharing, Allan. Much appreciated. :-)
BR / Ole
Please sign in to leave a comment.