# csv2tbl.py - M.Keranen (mksql@yahoo.com) [10/14/2004] # ------------------------------------------------------------------------ # Read a CSV file (exported from Excel), and generate an SQL statement # to create a matching table structure (by field names and widths only). # Expects sheet to start in cell A1, column names in first row: # Suggested Excel export steps: # 1) Delete blank leading rows and columns to bring start of sheet to A1 # 2) Select all cells, left justify, Format->Column->Auto Fit Selection # 3) Save as file type .CSV # ------------------------------------------------------------------------- import csv, os, string, sys if len(sys.argv)<2: print "\nUsage: csv2tbl.py path/datafile.csv (0,1,2,3 = column name format):" print "\nFormat: 0 = TitleCasedWords" print " 1 = Titlecased_Words_Underscored" print " 2 = lowercase_words_underscored" print " 3 = Words_underscored_only (leave case as in source)" sys.exit() else: if len(sys.argv)==2: dummy, datafile, = sys.argv namefmt = '0' else: dummy, datafile, namefmt = sys.argv namefmt = int(namefmt) #outfile = os.path.basename(datafile) tblname = os.path.basename(datafile).split('.')[0] outfile = os.path.dirname(datafile) + '\\' + tblname + '.sql' # Create string translation tables allowed = ' _01234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ' delchars = '' for i in range(255): if chr(i) not in allowed: delchars = delchars + chr(i) deltable = string.maketrans(' ','_') # Create list of column [names],[widths] reader = csv.reader(file(datafile),dialect='excel') row = reader.next() nc = len(row) cols = [] for col in row: # Format column name to remove unwanted chars col = string.strip(col) col = string.translate(col,deltable,delchars) fmtcol = col if namefmt < 3: # Title case individual words, leaving original upper chars in place fmtcol = '' for i in range(len(col)): if col.title()[i].isupper(): fmtcol = fmtcol + col[i].upper() else: fmtcol = fmtcol + col[i] if namefmt == 2: fmtcol = col.lower() if namefmt == 0: fmtcol = string.translate(fmtcol,deltable,'_') # Remove underscores d = 0 dupcol = fmtcol while dupcol in cols: d = d + 1 dupcol = fmtcol + '_' + str(d) cols.append([dupcol,1]) # Determine max width of each column in each row rc = 0 for row in reader: rc = rc + 1 if len(row) == nc: for i in range(len(row)): fld = string.strip(row[i]) if len(fld) > cols[i][1]: cols[i][1] = len(fld) else: print 'Warning: Line %s ignored. Different width than header' % (rc) print sql = 'CREATE TABLE %s\n(' % (tblname) for col in cols: sql = sql + ('\n\t%s NVARCHAR(%s) NULL,' % (col[0],col[1])) sql = sql[:len(sql)-1] + '\n)' sqlfile = open(outfile,'w') sqlfile.write(sql) sqlfile.close print '%s created.' % (outfile)