CSV Import is a dream

[sitestreet]

I used the CSV Import action last night for the first time. I must be completely settled in my knowledge of Wappler and how it all works because I got it right first time! What an excellent feature it is. Now the client can upload their data themselves.

[updates]

Wappler gives you satisfaction when it allows you to do things that you think are difficult with extreme simplicity and speed …

[mgaussie]

Csv export is equally great!

[sitestreet]

Yep, I’ve used that before and you’re right. I just thought the import process would be harder work but it was a breeze.

[UKRiggers]

And what do you use CSV Import for? Can you explain how it’s used and what for?

[TomD]

One option, which may not be obvious from the name, is that you’re not restricted to CSV import. Tab-separated files are very common and can be imported equally well, by specifying the delimiter.

The feature certainly works very well and is easy to use. However, if you need to import a lot of data, there may be better alternatives. I was getting script timeouts importing a lot of data. Using LOAD DATA INFILE (in MySQL) for the same import took less than a second.

[sitestreet]

I am building a CRM for a company who are currently using a system built in Access so they already have lots of data. Most is starting again but there’s a lot which they want to just migrate across so this gives the facility for them to export a batch of data (probably 50-100 records at a time) and then import it straight into the new system. They won’t need to send me it and wait for me to process it, they can just do it themselves as needed.

[revjrblack]

Any suggestions on how to automate table column names edits during the import of csv files from bank institutions, etc…? For instance, removing spaces, and other quirky none friendly MySQL column names.

[George]

Use the new Data Transformations to rename columns first and then export it. See:

Using Data Transformations in Server Connect

Data Transformations Data Transformation options are available in Server Connect. Using them you can easily combine different data sources, add, rename or remove columns from an existing data set. The options available in the Data Transformations are not editing your actual data set, they clone the data and allow you to manipulate it, so your original data source remains unchanged. You can use the Data Transformations with any collection/array such as - Database Query, API Data Source or any…

[revjrblack]

Wow, what a great tool! Thank you George.

[Philip_J]

Hey @sitestreet,

Just wondering if your process for this is something like this:

• Use import csv step in server connect to grab csv file from location (lets say client uploaded the file on the server)
• Run a repeat step on the import csv
• Inside the repeat, run a standard INSERT / UPDATE / UPSERT statement for every row of the imported file

Just wondering if this is the standard / best way as i believe that with the Wappler repeat, its creating a new server request for every insert statement, this can get quite slow if there are lots of rows in that repeat.

Is there a better more ‘bulk insert’ way of doing it?

Cheers

[sitestreet]

@Philip_J

Just wondering if this is the standard / best way as i believe that with the Wappler repeat

Yep, that's exactly how I did it. It's only processing about 50-100 records at a time but I would imagine that even thousands of records wouldn't take very long.

[TomD]

@Philip_J

Is there a better more ‘bulk insert’ way of doing it?

This thread might be helpful, particularly if you need to import a large number of records.

[Philip_J]

Thanks @sitestreet and @TomD.

Good to know it’s fairly easy for low volume inserts…and for bulk options that thread is a good read that I will need to look into in more detail.

Cheers!