| Subject | Re: import a csv file from excel |
| From | Bernard Mouille <bernardmouille16@gmail.com> |
| Date | Thu, 03 Aug 2023 01:33:48 -0400 |
| Newsgroups | dbase.getting-started |
| Attachment(s) | Test.txt |
The csv file sample is here : http://news.dbase.com/newsgroups.php?art_group=dbase.programming&article_id=69386 If you have Apache Libre Office Calc installed on your computer, you can thry with the Chris csv file. For your csv file, you must modify the parameters. Regards, Bernard. ED FURCHE Wrote: > how long should it take to import an excel.csv file of 2400 recs to a dbf? > dbf is about 100 characters | |
/* Test.prg Convert GuestRequest.csv file to dbf table with Libre Office. Question by Chris Faulkner ( GuestRequest.csv ) : http://news.dbase.com/newsgroups.php?art_group=dbase.programming&article_id=69386 My code post ( GuestRequest.csv ) : http://news.dbase.com/newsgroups.php?art_group=dbase.programming&article_id=69425 Using dBase 2.01, Windows 10, Libre Office 7.5.3.2, fr date format ( DD/MM/YYYY ) This code is a basic sample for tests. For csv type, look page 42 of : https://www.openoffice.org/api/basic/man/tutorial/tutorial.pdf For encoding : https://www.iana.org/assignments/character-sets/character-sets.xhtml Fields array options. aFields.[ i, 1 ] = Types for Calc. aFields.[ i, 2 ] = Field name for new table. aFields.[ i, 3 ] = Field type for new table. aFields.[ i, 4 ] = Field len for new table. aFields.[ i, 5 ] = Field dec for new table. aFields.[ i, 6 ] = Date and Timestamp = format ( en, fr, sql ) Numeric = Decimal separator ( < . > or < , > ) aFields.[ i, 7 ] = Free for later. This code is a basic sample. */ // Select the csv file to convert. #define CSV_GUESTREQUEST // Csv file to convert -> GuestRequest.csv //#define CSV_MYFILE_CSV // My csv file to test -> MyFile.csv local tTimeStart parameter cPath // Its only for me. try ; set directory to &cPath // Its only for me. catch (exception e) ; endtry ; release cPath // Its only for me. tTimeStart = datetime() // Begin execution time. // <Private parameters> pub_lDisplay = true // Display Calc : true = yes, false = no. pub_lError = false // Error found when execute. pub_lWarning = false // Warning found when execute. pub_aFields = new array() // Fields array options. pub_cTableTmp = set( "directory" ) + "\_Result_Table_Tmp.dbf" // Dbf Temp table. pub_cTableOut = set( "directory" ) + "\_Result_Table_Out.dbf" // Dbf table created. pub_cFileLog = set( "directory" ) + "\_Result_log.log" // Log file. #ifdef CSV_GUESTREQUEST pub_cFileCsv = set( "directory" ) + "\GuestRequest.csv" // Csv file to open. // Csv options. pub_cListSeparator = 44 // ASCII Field separator : 44 = commas. pub_cFieldDelimiter = 34 // ASCII Text field delimiter : 34 = double quotes. pub_cEncoding = 76 // Encoding : 76 = Unicode (UTF-8). pub_cFirstLine = 1 // First line to be treated in csv file. // Fields array options. bd_aadd( pub_aFields, { 1, "entry_id" , "numeric" , 9, 0, "" , "" } ) // Col 1 - A type 1 = NUMERIC, Entry Id -> ENTRY_ID bd_aadd( pub_aFields, { 2, "entry_date" , "timestamp", 0, 0, "sql", "" } ) // Col 2 - B type 2 = TEXT , Entry Date -> ENTRY_DATE bd_aadd( pub_aFields, { 1, "sponsor_member_ID", "numeric" , 9, 0, "" , "" } ) // Col 3 - C type 1 = NUMERIC, Sponsor's Member ID -> SPONSOR_S_ bd_aadd( pub_aFields, { 2, "sponsor_name" , "char" , 24, 0, "" , "" } ) // Col 4 - D type 2 = TEXT , Sponsor's Name -> SPONSOR_S2 bd_aadd( pub_aFields, { 2, "sponsor_phone" , "char" , 17, 0, "" , "" } ) // Col 5 - E type 2 = TEXT , Sponsor's Phone -> SPONSOR_S3 bd_aadd( pub_aFields, { 2, "sponsor_email" , "char" , 64, 0, "" , "" } ) // Col 6 - F type 2 = TEXT , Sponsor's Email -> SPONSOR_S4 bd_aadd( pub_aFields, { 2, "arrival_date" , "date" , 0, 0, "en" , "" } ) // Col 7 - G type 2 = TEXT , Arrival Date -> ARRIVAL_DA bd_aadd( pub_aFields, { 2, "leave_date" , "date" , 0, 0, "en" , "" } ) // Col 8 - H type 2 = TEXT , Leave Date -> LEAVE_DATE bd_aadd( pub_aFields, { 1, "vacation_days" , "numeric" , 4, 0, "" , "" } ) // Col 9 - I type 1 = NUMERIC, Vacation Days -> VACATION_D bd_aadd( pub_aFields, { 2, "guest_name" , "char" , 24, 0, "" , "" } ) // Col 10 - J type 2 = TEXT , Guest Name -> GUEST_NAME bd_aadd( pub_aFields, { 2, "guest_email" , "char" , 64, 0, "" , "" } ) // Col 11 - K type 2 = TEXT , Guest Email -> GUEST_EMAI bd_aadd( pub_aFields, { 2, "card_status" , "char" , 5, 0, "" , "" } ) // Col 12 - L type 2 = TEXT , Card Status -> CARD_STATU bd_aadd( pub_aFields, { 2, "consent" , "char" , 8, 0, "" , "" } ) // Col 13 - M type 2 = TEXT , Consent -> CONSENT #endif // CSV_GUESTREQUEST #ifdef CSV_MYFILE_CSV // Works with french country. pub_cFileCsv = set( "directory" ) + "\MyFile.csv" // Csv file to open. set procedure to MyFileCsvCreate.cc additive MyFileCsvCreate( pub_cFileCsv ) // Csv options. pub_cListSeparator = 59 // ASCII Field separator : 59 = Semicolon. pub_cFieldDelimiter = 34 // ASCII Text field delimiter : 34 = double quotes. pub_cEncoding = 1 // Encoding : 1 = maybe ANSI ? or Windows1252/WinLatin1 ? or default system ?. pub_cFirstLine = 1 // First line to be treated in csv file. // Fields array options. bd_aadd( pub_aFields, { 2, "text_ascii_sup" , "char" , 16, 0, "" , "" } ) // Col 1 - A type 2 = TEXT , TextAsciiSup -> TEXTASCIIS bd_aadd( pub_aFields, { 1, "number_decimal_fr" , "numeric", 12, 4, "" , "" } ) // Col 2 - B type 1 = NUMERIC, NumberDecimalFR -> NUMBERDECI bd_aadd( pub_aFields, { 2, "number_dec_text_fr", "numeric", 12, 4, "," , "" } ) // Col 3 - C type 2 = TEXT , NumberDecTextFR -> NUMBERDEC2 bd_aadd( pub_aFields, { 4, "date_fr_date" , "date" , 0, 0, "" , "" } ) // Col 4 - D type 4 = DATE FR, DateFrDate -> DATEFRDATE bd_aadd( pub_aFields, { 2, "date_fr_text" , "date" , 0, 0, "fr", "" } ) // Col 5 - E type 2 = TEXT , DateFrText -> DATEFRTEXT #endif // CSV_MYFILE_CSV // </Private parameters> bd_tErase( pub_cTableTmp ) bd_tErase( pub_cTableOut ) bd_fErase( pub_cFileLog ) ? set alte to &pub_cFileLog set alte on ? "Convert csv file to dbf7 table." ? ? "File csv in : " + pub_cFileCsv ? "Table dbf7 out : " + pub_cTableOut ? if not new file().exists( pub_cFileCsv ) ? "***Error, file not exists : " + pub_cFileCsv pub_lError := true endif if not pub_lError ca_CsvToDbf3_GetDbf() endif if not pub_lError ca_TableTransform() endif ? ? "Execution time : " + bd_DTNtoDMHdiff( datetime() - tTimeStart ) ? set alte off set alte to if pub_lError or pub_lWarning bd_ShellExecute( pub_cFileLog ) endif return // Tools library functions Work only for GuestRequest.csv file. // Convert a table dbf3 created from a csv file with Libre Office Calc to a table dbf7. function ca_TableTransform // Works only for GuestRequest.csv file. local i // Numeric count. local cCreate // String for create the table. local nRecord // Record in use. local qIn // Table created by calc Query object. local qOut // Table out for use Query object. local xValue // Field value read for write. cCreate = 'Create table "' + pub_cTableOut + '" (' for i = 1 to alen( pub_aFields, 1 ) cCreate += pub_aFields[ i, 2 ] + " " // Field name. do case case pub_aFields[ i, 3 ] == "char" cCreate += "char(" + bd_ntos( pub_aFields[ i, 4 ] ) + ")," case pub_aFields[ i, 3 ] == "numeric" cCreate += "numeric(" + bd_ntos( pub_aFields[ i, 4 ] ) + "," + bd_ntos( pub_aFields[ i, 5 ] ) + ")," case pub_aFields[ i, 3 ] == "date" cCreate += "date"+ "," case pub_aFields[ i, 3 ] == "timestamp" cCreate += "timestamp"+ "," otherwise ? "***Error to create the field " + bd_ntos( i ) endcase endfor cCreate := left( cCreate, len( cCreate ) - 1 ) + ")" // Remove the last << , >>. ? "Creation table options :" ? cCreate ? try _app.databases[ 1 ].executeSql( cCreate ) catch (exception e) ? "***Error to create table : " + e.message ? "** " + cCreate pub_lError := true endtry cCreate := "" if not pub_lError nRecord = 0 use "&pub_cTableOut" list structure use qIn = new Query() qIn.sql := "select * from '" + pub_cTableTmp + "'" qIn.active := true qOut = new Query() qOut.sql := "select * from '" + pub_cTableOut + "'" qOut.active := true do while not qIn.Rowset.endOfSet nRecord++ qOut.rowset.beginAppend() for i = 1 to alen( pub_aFields, 1 ) xValue = qIn.rowset.fields[ i ].value do case case pub_aFields[ i, 3 ] == "char" if not pub_aFields[ i, 1 ] == 2 xValue := null endif case pub_aFields[ i, 3 ] == "date" if pub_aFields[ i, 1 ] == 4 // Date FR, do nothing. elseif pub_aFields[ i, 1 ] == 2 // Text. xValue := bd_dStrWithOptToD( xValue, pub_aFields[ i, 6 ] ) else xValue := null endif case pub_aFields[ i, 3 ] == "numeric" if pub_aFields[ i, 1 ] == 1 // Do nothing. elseif pub_aFields[ i, 1 ] == 2 // Numeric value in text. xValue := bd_nStrWithOptToN( xValue, pub_aFields[ i, 6 ] ) else xValue := null endif case pub_aFields[ i, 3 ] == "timestamp" if pub_aFields[ i, 1 ] == 2 xValue := bd_tStrWithOptToT( xValue, pub_aFields[ i, 6 ] ) else xValue := null endif otherwise xValue := null endcase if not xValue == null qOut.rowset.fields[ i ].value := xValue else pub_lWarning := true ? "$$$Warning in field " + bd_ntos( i ) + " record " + bd_ntos( nRecord ) + " cell " + bd_IntToCell( i, nRecord + 1 ) endif endfor qIn.rowset.next() enddo qOut.rowset.Save() qOut.active := false qIn.active := false endif return // Create a dbf3 table from a csv file with Apache Libre Offic Calc. // Using public variable for parameters. function ca_CsvToDbf3_GetDbf() local oVb // VBScript object. local cOptions // Options to save to dbf table 3. local i // Count variable. oVb = new OleAutoClient( "msscriptcontrol.scriptcontrol" ) oVb.language := "vbscript" // Open Libre Office. oVb.addcode( 'Set oServiceManager = CreateObject( "com.sun.star.serviceManager" )' ) oVb.addcode( 'Set Desktop = oServiceManager.createInstance( "com.sun.star.frame.Desktop" )' ) // Config for open a csv file. oVb.addcode( 'Dim args(2)' ) // Array for Desktop. oVb.addcode( 'Set args(0) = oServiceManager.Bridge_GetStruct( "com.sun.star.beans.PropertyValue" )' ) oVb.addcode( 'args(0).name = "Hidden"' ) // Create a bridge for display or hide. oVb.addcode( 'args(0).value = ' + iif( not pub_lDisplay, "true", "false" ) ) // true = display ( default ), false = hide. oVb.addcode( 'Set args(1) = oServiceManager.Bridge_GetStruct( "com.sun.star.beans.PropertyValue" )' ) oVb.addcode( 'args(1).Name = "FilterName"' ) oVb.addcode( 'args(1).Value = "Text - txt - csv (StarCalc)"' ) oVb.addcode( 'Set args(2) = oServiceManager.Bridge_GetStruct( "com.sun.star.beans.PropertyValue" )' ) oVb.addcode( 'args(2).Name = "FilterOptions"' ) // Create the options for open the csv file. cOptions = bd_ntos( pub_cListSeparator ) + ',' cOptions += bd_ntos( pub_cFieldDelimiter ) + ',' cOptions += bd_ntos( pub_cEncoding ) + ',' cOptions += bd_ntos( pub_cFirstLine ) + ',' for i = 1 to alen( pub_aFields, 1 ) cOptions += bd_ntos( i ) + "/" + bd_ntos( pub_aFields[ i, 1 ] ) + iif( i == alen( pub_aFields, 1 ), "", "/" ) endfor ? "CSV options string for open the csv file :" ? cOptions ? oVb.addcode( 'args(2).Value = "' + cOptions + '"' ) // Test_csv.csv try oVb.addcode( 'Set oDoc = Desktop.loadComponentFromURL("' ; + bd_FileToUrl( pub_cFileCsv ) +'", "_blank", 0, args )' ) catch ( exception e ) ? "***Error " + e.message + chr( 10 ) + "***File : " + pub_cFileCsv + " not open." pub_lError := true endtry if not pub_lError // Save dbf table config. oVb.addcode( 'Dim args_SaveDbf(0)' ) // Array for Save dbf files. oVb.addcode( 'Set args_SaveDbf(0) = oServiceManager.Bridge_GetStruct( "com.sun.star.beans.PropertyValue" )' ) oVb.addcode( 'args_SaveDbf(0).Name = "FilterName"' ) oVb.addcode( 'args_SaveDbf(0).Value = "dBase"' ) try oVb.addcode( 'oDoc.storeAsURL "' + bd_FileToUrl( pub_cTableTmp ) + '", args_SaveDbf' ) catch ( exception e ) ? "***Error " + e.message + chr( 10 ) + "***Table : " + pub_cTableTmp + " not save" pub_lError := true endtry endif oVb.addcode( 'oDoc.Close( True )' ) oVb.addcode( 'Desktop.Terminate()' ) oVb := null if not pub_lError use "&pub_cTableTmp" list structure use endif return // Transform a str date to a date ignore international options. function bd_dStrWithOptToD( cValue, cOption ) local nYear, nMonth, nDay, tReturn nYear = val( subs( cValue, 7, 4 ) ) do case case cOption == "en" // Format MM/DD/YYYY nMonth = val( subs( cValue, 1, 2 ) ) - 1 nDay = val( subs( cValue, 4, 2 ) ) case cOption == "fr" // Format DD/MM/YYYY nMonth = val( subs( cValue, 4, 2 ) ) - 1 nDay = val( subs( cValue, 1, 2 ) ) otherwise return null endcase tReturn = dttod( new date( nYear, nMonth, nDay ) ) return tReturn // Transform a str numeric value to a number ignore international options. function bd_nStrWithOptToN( cValue, cOption ) local c, i, cReturn cReturn = "" for i = 1 to len( cValue ) c = subs( cValue, i, 1 ) do case case c$"-0123456789" cReturn += c case c == cOption cReturn += set( "point" ) endcase endfor return val( cReturn ) // Transform a str timestamp to a timestamp ignore international options. function bd_tStrWithOptToT( cValue, cOption ) local nYear, nMonth, nDay, nHour, nMinute, nSeconds, tReturn do case case cOption == "sql" // Format YYYY-MM-DD hh:mm:ss nYear = val( subs( cValue, 1, 4 ) ) nMonth = val( subs( cValue, 6, 2 ) ) - 1 nDay = val( subs( cValue, 9, 2 ) ) nHour = val( subs( cValue, 12, 2 ) ) nMinute = val( subs( cValue, 15, 2 ) ) nSecond = val( subs( cValue, 18, 2 ) ) otherwise return null endcase tReturn = new date( nYear, nMonth, nDay, nHour, nMinute, nSecond ) return tReturn // Tools library functions. // Add one row to an array. function bd_aadd( aArray, xData ) local i local l local n if bd_valtype( xData ) == "A" l = xData.size n = ( aArray.size / l ) + 1 aArray.resize( n, l ) for i = 1 to l aArray[ n, i ] := xData[ i ] endfor else aArray.add( xData ) endif return // Transform a directory name with ("..\") in string ("c:\"). function bd_DirPointToString( cDir ) local f // File object. local cReturn // Directory name transformed. local cFileBat // File name of the batch file. local cFileDos // File created with Dos. cFileBat = set( "directory" ) + "\DirPointToString_" + bd_Millisec( true ) + ".bat" cFileDos = set( "directory" ) + "\DirPointToString_" + bd_Millisec( true ) + ".fbm" f = new File() f.delete( cFileBat ) f.delete( cFileDos ) f.create( cFileBat, "A" ) f.puts( "cd " + cDir ) f.puts( "echo %cd% >" + cFileDos ) f.close() bd_RunVBs('cmd /K "' + cFileBat + '" &exit', 0, true ) f.delete( cFileBat ) f.open( cFileDos, "R" ) cReturn = trim( f.gets() ) f.close() f.delete( cFileDos ) return cReturn // Convert datetime() difference in day(s), hour(s), minute(s), second(s) and milliSecond(s). function bd_DTNtoDMHdiff( nDiff ) local wDiff local cReturn local nTemp wDiff = nDiff cReturn = "" nTemp = int( wDiff ) if nTemp > 0 cReturn += bd_ntos( nTemp ) + " day" + iif( nTemp > 1, "s ", " " ) endif wDiff := wDiff - int( wDiff ) nTemp := int( wDiff * 24 ) if nTemp > 0 cReturn += bd_ntos( nTemp ) + " hour" + iif( nTemp > 1, "s ", " " ) endif wDiff := wDiff - ( nTemp / 24 ) nTemp := int( wDiff * 24 * 60 ) if nTemp > 0 cReturn += bd_ntos( nTemp ) + " minute" + iif( nTemp > 1, "s ", " " ) endif wDiff := wDiff - ( nTemp / ( 24 * 60 ) ) nTemp := int( wDiff * 24 * 60 * 60 ) if nTemp > 0 cReturn += bd_ntos( nTemp ) + " second" + iif( nTemp > 1, "s ", " " ) endif wDiff := wDiff - ( nTemp / ( 24 * 60 * 60 ) ) nTemp := int( wDiff * 24 * 60 * 60 * 1000 ) if nTemp > 0 cReturn += bd_ntos( nTemp ) + " millisecond" + iif( nTemp > 1, "s ", " " ) endif return cReturn // Erase a File. function bd_fErase( cTable ) if new file().exists( cTable ) new file().delete( cTable ) endif return // Transform a file name with ("..\") in string ("c:\"). function bd_FilePointToString( cFile ) local cReturn // File name transformed. local sFile // Name of the file without directory. sFile = bd_StringExtractRight( cFile, "\" ) cReturn = bd_StringExtractLeft( cFile, "\" ) cReturn = bd_DirPointToString( cReturn ) cReturn += "\" + sFile return cReturn // Convert a file name to Url. function bd_FileToUrl( cFile ) local i // Count variable. local cReturn // Return Url name. local wFile // File to transform in work. if at( "/", cFile ) > 0 // Nothing to do. return cFile endif i = 1 cReturn = "" wFile = ( cFile ) if at( "\", wFile) = 0 // Not back slash. wFile := set( "directory") + "\" + wFile endif if at( "..\", wFile ) > 0 wFile := bd_FilePointToString( wFile ) endif do while i <= len( wFile ) if subs( wFile, i, 1 ) = "\" cReturn += "/" else cReturn += subs( wFile, i, 1 ) endif i++ enddo cReturn = "file:///" + cReturn return cReturn // Convert number Column and number Row in letter columns and number row. function bd_IntToCell( nRow, nCol, lLong ) local n local cReturn if argcount() < 3 lLong = false endif cReturn = "" n = abs( nCol ) do while n > 0 if mod( n, 26 ) = 0 // For the "Z" character. cReturn := "Z" + cReturn n = int( n / 26 ) - 1 else cReturn := chr( 64 + mod( n, 26 ) ) + cReturn n = int( n / 26 ) endif enddo cReturn += bd_ntos( nRow ) + iif( not lLong, "", " (" + bd_ntos( nRow ) + "," + bd_ntos( nCol ) + ")" ) return cReturn // Returns the milli seconds of the hour. function bd_MilliSec( lString ) local d local xReturn if argcount() = 0 lString = false endif d = new Date() xReturn = right( str( d.getTime(), 20, 0 ), 3 ) if not lString xReturn = val( xReturn ) endif return xReturn // Convert a number in string without space before. function bd_ntos( nNum, nInt, nDec ) if argcount() < 2 nInt := 16 endif if argcount() < 3 nDec := 0 endif return ltrim( str( nNum, nInt, nDec ) ) // Run a command with options hidden and/or stop dBase. function bd_RunVBs( cCommand, nDisplay, lStop ) local oVBscript // Object for VBscript. if argcount() < 3 lStop = true // true = Stop dBasePLUS, false = not stop dBasePlus. endif if argcount() < 2 nDisplay = 1 // 1 = Display normal cExe, 0 = Hidden. endif oVBscript = new OleAutoClient( "WScript.Shell" ) oVBscript.Run( cCommand, nDisplay, lStop ) oVBscript := null return // Execute a command with options. function bd_ShellExecute( xHorCom, cOp, cFile, cPara, cDir, nShow ) if type( "ShellExecuteA" ) <> "FP" extern CHANDLE ShellExecuteA( CHANDLE, CSTRING, CSTRING, CSTRING, CSTRING, CINT ) Shell32.dll endif if argcount() == 1 ShellExecuteA( null , "open", xHorCom, null , null, 1 ) else ShellExecuteA( xHorCom, cOp , cFile , cPara, cDir, nShow ) endif return // Return the left string before 1 selected char( eg. directory name ). function bd_StringExtractLeft( sString, cComma ) local cReturn local nRat nRat = rat( cComma, sString ) cReturn = left( sString, nRat - 1 ) return cReturn // Return the right string after 1 string Comma ( eg. file name ). function bd_StringExtractRight( sString, cComma ) return right( sString, len( sString ) - rat( cComma, sString ) - len( cComma ) + 1 ) // Erase a table and his files. function bd_tErase( cTable ) if _app.databases[ 1 ].tableExists( cTable ) _app.databases[ 1 ].dropTable( cTable ) endif return // Return the type of a value or a variable. function bd_ValType( xData ) local cReturn private PRIVATE_xData PRIVATE_xData = xData cReturn = type( "PRIVATE_xData" ) PRIVATE_xData := null release PRIVATE_xData return cReturn | |