S3 CSV file dataset failures

Question & Answer
, , , ,
1

Hi! I have a csv file that is stored in a S3 bucket that I would like to create into a dataset. When I create a new dataset and upload, the refresh fails with errors on multiple columns. When I export the error file I notices a lot of fields that are incorrectly placed, ex) an id field should only contain a 5digit number but I see names from other columns in that field.

The csv is created in Alteryx with these output options.
image

I’m guessing there is an issue with QuickSight noticing the delimiter and that is why some values are shifted. Also I have some columns that are full sentences that include commas.

Does anybody have a suggestion on how to fix? If I change the delimiter to | or } will QuickSight notice and upload the csv file?

2

Hi @ryann.schuessler - Can you please share the error details. CSV should work fine in QuickSight and by default it took the comma separator. You can change the delimiter of the csv file. If possible, please share sample data with error records to analyze in details ( Please mask PII data if there is any).

Regards - Sanjeeb

3

hi @ryann.schuessler ,

take a look at the options available in manifest file : Supported formats for Amazon S3 manifest files - Amazon QuickSight and reupload the file.

Regards,
Koushik

4

Here is the refresh error details
image

This is an example of the output error, in the original csv file all PRD_IDs are numeric. The fields under PRD_ID that are text should be under different columns
image

5

check the manifest file for text qualifier.

6

This is the manifest file I am currently using

{
“fileLocations”: [
{
“URIs”: [
“https://example/example.csv”
]
}
],
“globalUploadSettings”: {
“textqualifier”: “"”
}
}

Do I need to change the text qualifier?

7

image

The text qualifier has a forward slash, for some reason when I copied the file text it got removed in the comment

8

Hi Ryann,

Did you manage to solve it? I’m facing the same issue and tried different things but still struggling when import to Spice.
Thanks!!

9

Hello @ryann.schuessler, @Sanjeeb2022 , and @Koushik_Muthanna !

@ryann.schuessler Are you still having trouble with this problem, or were you are able to find a solution? If you were able to find a solution could you post it to help the community?

Did you remove that forward slash and were able to solve the problem?

10

Hi! I was not able to find a solution. I had three columns with fields that included long JSON strings. I had to remove them from the dataset in order for it to refresh correctly.

Those fields weren’t necessary for my analysis, but I’m sure I’ll run into this issue again in the future

11

Hey @ryann.schuessler !

After reading through the full question, I think that it would be beneficial to create a support ticket with AWS. @Koushik_Muthanna do you have any other suggestions?

Here are the steps to open a support case. If your company has someone who manages your AWS account, you might not have direct access to AWS Support and will need to raise an internal ticket to your IT team or whomever manages your AWS account. They should be able to open an AWS Support case on your behalf.

CC: @pilloren

1 Like