Parsing first row of csv into sql table using batch file

Although I don't know the format of SQL tables, I can show you how to read a CSV file. The Batch file below read all lines from the file; it first take field names from first line (CSV header) and create an array of variable names (eliminating possible spaces in field names); then it read the rest of lines and assign each field value to its corresponding Batch variable.

ProcessCSV.BAT:

@echo off
rem General-purpose CSV file reader program
rem Antonio Perez Ayala

setlocal EnableDelayedExpansion
set numFields=0
rem Read the lines of the CSV file
for /F "delims=" %%a in (CSVfile.csv) do (
   set "line=%%a"
   if !numFields! equ 0 (
      rem It is the first line: break it into an array of field names (removing spaces)
      for %%b in (!line: ^=!) do (
         set /A numFields+=1
         set name[!numFields!]=%%b
      )
   ) else (
      rem Replace spaces by Ascii-128 (to avoid split values that may have spaces)
      set "line=!line: =Ç!"
      rem Insert any char. at beginning of each field, and separate fields with spaces
      set i=0
      for %%b in (X!line:^,^= X!) do (
         set "field=%%b"
         rem Recover spaces in this field, if any
         set "field=!field:Ç= !"
         rem And assign it to corresponding variable (removing first character)
         set /A i+=1
         for %%i in (!i!) do set "!name[%%i]!=!field:~1!"
      )
      rem At this point all variables have the values of current record.
      rem They may be accessed explicitly (ie, from example CSVfile.csv):
      echo/
      echo Record of !FirstName! !LastName!
      rem ... or implicilty via the NAME array:
      for /L %%i in (3,1,!numFields!) do (
         for %%b in (!name[%%i]!) do echo    %%b: !%%b!
      )
   )
)

CSVfile.csv:

First Name,Last Name,Address,Postal Code,Company,Departament,Floor,Phone,Mobile
John,Smith,123 Fake Street,45612,SomeCo,Accounting,4,123-555-5555,123-555-5556
Jane,Doe,123 Fake Street,,SomeCo,,4,123-555-5555,123-555-5556

output:

Record of John Smith
   Address: 123 Fake Street
   PostalCode: 45612
   Company: SomeCo
   Departament: Accounting
   Floor: 4
   Phone: 123-555-5555
   Mobile: 123-555-5556

Record of Jane Doe
   Address: 123 Fake Street
   PostalCode:
   Company: SomeCo
   Departament:
   Floor: 4
   Phone: 123-555-5555
   Mobile: 123-555-5556

Please be aware that this program use several advanced Batch techniques. I suggest you to get help on every command you don't completely understand (ie: SET /?) and read it carefully. If after this process you have further questions about this program, just post they as an edit in your original question.

The most complex part of this program is responsible to assign empty strings to variables when the corresponding field is empty (two commas side by side); if the file have not empty fields, the program may be somewhat simpler. Also, this program (as most Batch solutions) may give erroneous results if certain special Batch characters appear in the file, like !. Most of these characters may be managed if required via certain modifications in the program.

EDIT: Modified version when no empty fields exists

@echo off
rem CSV file reader program when no empty fields exist
rem Antonio Perez Ayala

setlocal EnableDelayedExpansion
set numFields=0
rem Read the lines of the CSV file
for /F "delims=" %%a in (CSVfile.csv) do (
   set "line=%%a"
   if !numFields! equ 0 (
      rem It is the first line: break it into an array of field names (removing spaces)
      for %%b in (!line: ^=!) do (
         set /A numFields+=1
         set name[!numFields!]=%%b
      )
   ) else (
      rem Replace spaces by Ascii-128 (to avoid split values that may have spaces)
      set "line=!line: =Ç!"
      rem Separate fields (using comma as standard Batch separator)
      set i=0
      for %%b in (!line!) do (
         set "field=%%b"
         rem Assign this field to corresponding variable, recovering spaces
         set /A i+=1
         for %%i in (!i!) do set "!name[%%i]!=!field:Ç= !"
      )
      rem At this point all variables have the values of current record.
      rem They may be accessed explicitly (ie, from example CSVfile.csv):
      echo/
      echo Record of !FirstName! !LastName!
      rem ... or implicilty via the NAME array:
      for /L %%i in (3,1,!numFields!) do (
         for %%b in (!name[%%i]!) do echo    %%b: !%%b!
      )
   )
)

Please note that the standard separators in FOR sets are comma, semicolon and equal-sign, besides spaces:

for %a in (one two,three;four=five) do echo %a

Previous program replace spaces by another character and use commas to separate fields. However, if the line may contain semicolons or equal-signs the fields will be splitted at that point, so in this case these characters must be changed for another ones before the FOR and recovered later, in the same way of the space.

EDIT: Modifications for new request (eliminate last comma)

Eliminating the last comma is not trivial, although not too complex neither. I hope my method be easy to understand; it is based on SET /P command behaviour of show text (the input prompt) with NO new line at end; note that the format is SET /P =text>>out<NUL. The <NUL part is needed so the SET /P will NOT wait for input; don't leave spaces before the < (the same as >>). However, this behaviour do NOT work in Windows Vista an later versions, I think. If the method don't work for you, then it must be modified again...

I also moved ahead and include some remarks about the parts that still are missing in your code (I think), that is, the processing of several files.

:proces
REM Executing loop for each file in current directory
REM *This may be done with a FOR loop:*
::*for %%F in (*.csv) do (*
    REM *The file name is given by %%F. In this case, the fileinitialization part*
    REM *must be done here, for example:*
    set numFields=0
    echo %creat%>fil.sql
    REM Read the lines of the CSV file
    For /F "eol==" %%A in (bio.csv) Do ( 
        set "line=%%A" 
        REM check if index of array is 0
        if !numFields! equ 0  (
            REM First line, Store in array name
            for %%B in (!line: ^=!) do (
                REM Note that I changed the place of the ECHO command
                set /A numFields+=1
                set name[!numFields!]=%%B
                if !numFields! equ 1 (
                    REM First field: show it with NO comma and NO NEW LINE
                    set /P =%%B (text^)>>%sqfile%<NUL
                ) else (
                    REM Next fields: complete the comma of previous field, WITH NEW LINE
                    echo ,>>%sqfile%
                    REM ... and show this field with NO comma and NO NEW LINE (again)
                    set /P =%%B (text^)>>%sqfile%<NUL
                )
            )
            REM Insert the new line of last field (that have NOT comma :-)
            echo/>>%sqfile%
        )
    )
::*)*
GOTO:EOF

:copydata

I strongly encourage you to keep my previous format: 4 justification columns inside each block of code enclosed in parentheses and place the closing parentheses in the same column of the opening command, FOR or IF. This format will help you to easily locate errors cause by mismatched parentheses in large programs.

Antonio