0

I have a csv file and I need to transfer all its data to MySQL with a click of a button using ASP Classic. I have searched a lot but I couldn't find an answer.

user1725573
  • 11
  • 2

2 Answers2

1

I would upload the file first using something like Pure ASP File Upload (there are alternatives) and then using this solution to read the file and then process the contents How to read CSV files line by line in VBScript

Community
  • 1
  • 1
pee2pee
  • 3,619
  • 7
  • 52
  • 133
0

What pee2pee said is the best method, as you will need a script in ASP-Classic that can handle the file upload. MySQL has a default directory it uses to exchange files that you will need to use unless you end up modifying --secure_file_priv and clear it like secure-file-priv = "". If you do this you can put the file anywhere and upload it, otherwise you have to check that variable with SHOW VARIABLES LIKE "secure_file_priv"; and see if it is cleared or locate and only use the directory stored in that variable.

Now this is the opposite of what you need, but I will give you this first. This is how you can get it to export data out of your database back into a CSV again. But keep in mind what I said about about the directory where you export or import files, as it won't work if you don't use the one it allows or unlock it to allow any, but keep in mind that later option is less secure, so you might set it back before deploying to a work environment unless you really trust your users.

SELECT * FROM table
INTO OUTFILE '/bin/export/yourdata.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

And the invert of that would be:

LOAD DATA INFILE 'yourdata.csv'
INTO TABLE t1
(column1, column2, column3, ...)
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\n';`

Once you get the directory setup and perhaps file privileges granted if needed, then get the an ASP upload script such as the one mentioned above, and use something like the code I provided to get it going once you have the ability to upload the file via the magic of an ASP Classic HTML button click.

easleyfixed
  • 219
  • 1
  • 13