I'm uploading a CSV file to AWS so I can query it with SQL, but names that contain commas are being split across multiple columns. For example, a name like "Smith, John" is being interpreted as two separate fields instead of one. How should I format or import the file so the complete name stays in a single column?
3 Answers
Check the process that generates the file and make sure it properly escapes fields containing commas. Another option is to use a different delimiter, such as a tab, and import the file as a TSV instead. Just make sure the table definition uses the same delimiter.
This is usually a CSV formatting issue rather than an AWS issue. Any text value containing a comma should be enclosed in double quotes, like "Smith, John". The CSV parser will then treat the comma as part of the name instead of a column separator.
If you’re querying the file directly, you can preprocess it first or configure the table parser with the correct delimiter and quoting rules. For larger datasets, converting the data to Parquet and querying it through a catalog and SQL service can also avoid many delimiter-related problems.
I’m new to this—does that mean I need to add the double quotes while creating the file, before uploading it?