CSV import headers not working with space

I am trying to allow a client to import data from and existing system via CSV. The report that their current system produces includes headers that have spaces (e.g. First name). I have set up the CSV import, which works but only if I manually change the headers to _ instead of space.

In the data picker in SC it adds a reference (within the repeat step) to the field as ['First name'] but this produces an ‘array to string conversion’ error in the dev tools, however as First_name it works ok.

Can we not reference headers that include spaces? Or is there a little bug in the CSV processing/repeat elements that are preventing it from working?

It’s best to always use camelCase or underscore when naming anything relative to DB table columns. Spaces between column names are not the best practice and should be avoided at all cost.

You can use the REPLACE() function to change the column names with spaces.

SQL Server REPLACE function overview

To replace all occurrences of a substring within a string with a new substring, you use the REPLACE() function as follows:

REPLACE(input_string, substring, new_substring);

In this syntax:

  • input_string is any string expression to be searched.
  • substring is the substring to be replaced.
  • new_substring is the replacement string.

The REPLACE() function returns a new string in which all occurrences of the substring
are replaced by the new_substring . It returns NULL if any argument is NULL.

1 Like

Thanks for that - my column names are all camelCase. The issue is that the CSV is coming from another system I have no control over. I could try and upload the CSV, manipulate the header row, then process it however, I think the data binding should be able to cope with object indexes that contain spaces.

Yeah, I’ve had to do the same. It’s a pain, but not much you can do otherwise.

Cheers. I found a workaround that appears to be fairly easy to implement. I have marked the CSV as having no headers which means I can name them myself, making sure there are no spaces. I set a value (I called it headproc) to flag when the first row had been processed. A condition is then used in the repeat with no action, apart from setting the headproc value to 1, when headproc == 0. This means the first row is essentially skipped and the rest can be processed.

The downside is that there’s no validation that the columns are in the correct order etc. which is usually handled by the Import CSV step. @patrick it would be really useful to get it working so that spaces can be processed.

1 Like

Supper job!

1 Like

Could you post the header line of the csv that you had problems with. Also please tell me if you used NodeJS, PHP or ASP.

Hi @patrick, thanks for looking at this.

I’m using php. Here’s the headers that cause the problem:
User ID,First name,Last name,Gender,Registered,Date of birth,Email,Telephone,Address 1,Address 2,City,Region,Postcode,Player teams,Coach teams

if they are changed to:
User_ID,First_name,Last_name,Gender,Registered,Date_of_birth,Email,Telephone,Address_1,Address_2,City,Region,Postcode,Player_teams,Coach_teams
it does work. In the data picker in Server Connect, the first example references a value such as {{['First name']}} whereas in the second it is just {{First_name}}

So the actual parsing of the csv does work with spaces in the header, but you can’t use them in expressions.

The expression {{['First name']}} is an array constructor, you want to access the data as an object member like {{data['First name']}} or {{$this['First name']}}. You have to access it like this because a space is not allowed in a variable name.

2 Likes

Thanks - the $this['First name'] worked for me; I assume that being in the repeat, $this identifies the current record. I didn’t realise $this was an option.

The main point, though, is that the data picker is producing the references. It isn’t adding the $this (if in a loop) or data (value name) if referring to another value. Could this be added?

Thousands of businesses still rely on Excel as their “data base”.

“camelCase” column naming for sql, mysql, etc can be easily maintained in our own databases but when a client relies on Excel and an Excel to Windows Access databases you have to have consistent CSV scripts that retain the CLIENT’s internal data format.

The client’s column names exactly as somebody 3 years ago or yesterday made up “friendly coumn names” are usually required in your client CSV exports so that their Excel xlsx file processes and/or .accdb or even the older .mdb database files know how to reference the appropriate values in-house.

So my internal mysql tables have sql happy field names while client import CSV files get mapped by my custom scripts and Save-it-Yourself Client exports from mysql get mapped back to the client Excel column names

I’ve had to write different export and import CSV files for huge international European businesses who still track operations, sales, form input, etc. via Excel as the Master “data” tool.

1 Like

This will be fixed in the next update.

Now long variable names with spaces in server connect, will follow the same convention as in app connect: _[“long name”]

1 Like

Fixed in Wappler 3.3.2

This topic was automatically closed after 31 hours. New replies are no longer allowed.