Help

Input.fileAsync force change ID to Date

Topic Labels: Scripting extentions
Solved
Jump to Solution
479 11
cancel
Showing results for 
Search instead for 
Did you mean: 

Input.fileAsync force change ID to Date.

//read csv
let latestStock = await input.fileAsync('Please CSV',
    {
        allowedFileTypes: ['.csv'],
    }
);

console.log(latestStock);

image
↑It’s true ID"0002-3". But Date format.

If Do you know resolve. Please teach me.

1 Solution

Accepted Solutions

Airtable is attempting to parse the data in the CSV file and incorrectly converting the value into a date.

Note that when you import a csv file you get both an array of the parsedContents and the file object itself. If you have no control over the source data, you are probably better off working with the file object itself and doing any parsing yourself.

const csvFile = await input.fileAsync("Pick a file")
console.log(csvFile)
const textContent = await csvFile.file.text()
const manualParsing = textContent.trim().split("\n").map(line => line.split(","))
console.log(manualParsing)

image

See Solution in Thread

11 Replies 11

JavaScript’s native date methods are pretty much useless.

The Temporal API should deal with a lot of existing pain points concerning dates, but it’s still at least a year away from being implemented, as far as I know.

With that said, I’m not 100% clear on what you’re asking. That value at index 0 is a date but shouldn’t be? Or is it just the format that’s the problem?

Thank you so much. I’m sorry for the late reply.

The value at index 0 is “0002-3”. it’s not date.
I think, problem is only format.

Can you post a screen shot of the original csv file?

Are you saying that 0002-3 is turning into Mar 2 2001 (the second day of the third month)?
Are you seeing the same behavior in all of the rows of data, or only this one?

This might be related to the scripting app’s auto-parser which there is unfortunately no way of opting-out of.

The issue that is linked was about numbers being put in scientific notation automatically, but I would assume that both issues arise from the same source. Unfortunately, the new scripting feature that was proposed in the post still is not available to my knowledge.

Yeah, no easy way to go about it. I just took a look at Airtable’s CSV importer (assuming that’s what you used) and from what I can tell, I’d be easier to reformat such invalid date formats than tackle the issue at its parsing core.

If you want a Scripting-app base solution, you should be able to get a Unix date out of that cell value with Date.parse() and maaybe some slicing. From that point onward, getting back to any format you desire would be straightforward, albeit cumbersome, since you’d be limited to just the JS core.

But given the circumstances, I’d advise another look at the wider picture here: Airtable’s formula field is a much better tool for tackling this formatting issue than a vanilla JS scripting environment. While lacking in some departments, they’re amazing when it comes to working with dates, so no need to reinvent the wheel.

Just parse all of the individual date parts (day, month, year, weekday, etc.) that you need in separate fields, then once you’re happy with the format, join them all together in a single one using a combination of the single ampersand operator (&) and unix-style newline escape sequences ("\n") as desired. This would probably take half an hour and leave you with just a single extra field in your table. Writing or modifying a script to handle this, on the other hand, could take much longer since the Scripting app is a way more powerful - and hence complex - tool.

Thank you so much. This is a screen shot.↓
The Yellow cell are is turning into Date.
image

Both are correct.

Thank you so much.
I gettin back to msec-format from 1970/1 / 1 00:00:00 by use of Date.parce().

but I can’t getting back to orignal str “0002-3”.

thank you very much for the useful information.

Airtable is attempting to parse the data in the CSV file and incorrectly converting the value into a date.

Note that when you import a csv file you get both an array of the parsedContents and the file object itself. If you have no control over the source data, you are probably better off working with the file object itself and doing any parsing yourself.

const csvFile = await input.fileAsync("Pick a file")
console.log(csvFile)
const textContent = await csvFile.file.text()
const manualParsing = textContent.trim().split("\n").map(line => line.split(","))
console.log(manualParsing)

image

Thank you so much :star_struck:
I got original ID in your way.

but My ID has additional " in each value.
How can I remove additional " ?

I think conect replace. but I couldn’t by my skill :sweat_drops:

image

This is csv text date.
image

And I have another problem.
image
Columun B Value has many comma, so line array length are unequal by use of line.split(",").

Open your csv file in a plain text editor and you will probably see that these extra quotes appear in the original file. CSV files often have text strings in double quotes in order to be able to include literal commas, quote marks, and other special characters in a single field.

Getting rid of the extra quotes is a matter of string manipulation. Keep in mind that some of the quotes you see are in the actual string, and other quotes are put there by Airtable to show that it is displaying a text string (versus a number or other data type).