Before you attempt this you need to add the CSV provider to your list of available sources.
In the business unit we already have two data source providers that can connect to CSV/TXT files and for the most part I would suggest using those. There is however a few times where this can be used. Especially because this can connect to a folder with multiple files with different columns and fields in each of them.
Go through this guide before you start.
- CSV Information
- CData Setup
- Connecting to one file
- Connecting to more than one file
- Connect to files on a FTP server
- The file is not using the default delimiter settings
- Getting the data
Here is a link to the file I use in this example
This one is set up with elements like this.
Year,Rank,Title,Gross $,Month Opened,Theatres 2005,1,Star Wars: Episode III - Revenge of the Sith,380270577.000000,2015-05,3663 2005,2,The Chronicles of Narnia: The Lion the Witch and the Wardrobe,291710957.000000,2015-12,3853 2005,3,Harry Potter and the Goblet of Fire,290013036.000000,2015-11,3858 2005,4,War of the Worlds,234280354.000000,2015-06,3910
Add a CData data source and choose the CSV provider.
The first step in setting up a CData connector should always be to click the ? in the top right corner.
You need to save the link in the information area as something csv and save it in a folder your user has rights to.
I saved mine in C:/Flat Files/Movies2005.csv. Then you just add a link to it in the Data Source field.
You can then set it up like this.
You just need to add the location of the file in the URI or Data Source field and then it should work.
If you have multiple files, you can connect to a folder. Like this
The location is containing multiple CSV files with different names but a similar setup.
You also have the option of connecting to a FTP server that contains CSV files. The connection string is as follows.
URI=ftps://localhost:990/folder1; User=user1; Password=password1;
I have a FTP server with a folder that contains some CSV files and here is how I set it up.
So I added my password to the Password field, my user account to the User field and in the URI field I added the location of the server. The default ftp port is 21.
As long as it is comma separated, this is all you need. If it is not comma separated, it is not as easy.
It still needs to be saved in that folder in C:/Flat Files/Movies2005.csv. Additionally, you need to create a file called schema.ini. It has to be named exactly that, or it wont work.
You can read about the various parameters by googling csv and schema.ini, but for this we need it setup like this.
First off you need to have the full file name as the first line in square brackets. Then you need to add each change to the file that is not default, mainly delimiters and such.
Then when you synchronize it looks at this file and finds the correct delimiter. This also works for multiple files, they just need to be added below with the same setup.
Like this for a folder.
Format=Delimited(;) [Sales Invoice Line.csv]
Are you connecting to files on a FTP, they also need the ini file added to its folder.
After setting up the provider, all needed is to synchronize/read the data source.
After that you can set up incremental load and make data type overwrites.
If your data doesn't come out as you expecting, go through the CData guide and see if you can turn on a setting that will give you the correct look.