Jump to content
TUFLOW Forum
tuflow support

Converting bc database.xls to csv

Recommended Posts

Q: Regarding the bc database set-up, how do I save the individual worksheets as .csv and retain these within the parent/host bc database.xls worksheet - as shown in in Chapter 4.10 of the Tuflow manual? Is it necessary to have them saved in this format? I’ve tried using the Tuflow tools.xls download and that seems to create individual .csv files outside of the parent worksheet.

A: The best way to work is probably the way you're doing it, ie. to have a spreadsheet that contains the bc_dbase sheet (usually the first sheet) and other sheets that contain the time-series data referenced by the bc_dbase sheet. To export these to .csv format, use the Save csv tool in TUFLOW Tools.xls. Unfortunately, you have to do this one sheet at a time (we need to look into providing an option for the TUFLOW Tools macros to export all sheets as .csv files). TUFLOW can't (yet) read .xls files directly, hence the need to export to .csv files.

Using a .xls spreadsheet to manage all of the data instead of directly working with .csv files keeps all of the data together and allows you to have charts and other information stored within the one file. You can also place charts etc on the sheets that you export to .csv (Excel only exports the tabular data when saving to .csv). The downside is that you must remember to press Save csv (ie. export to .csv) if you change the contents of a sheet, otherwise TUFLOW won't know about your changes.

Also see http://www.tuflow.com/forum/index.php?showtopic=268

Share this post


Link to post
Share on other sites

The attached spreadsheet has a macro that saves all worksheets to csv in one go.

The workbook can be stored anywhere on a network, and a button created with macro assignment pointing to the routine.

The VBA code is copied below for reference. I hope it helps make your TUFLOW modelling more efficient.

Private Sub WriteCsvForEachSheet()

Dim wbook As String

Dim csvname As String

Dim Fullcsvname As String

Dim ext As String

On Error Resume Next

wbook = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

Set fs = CreateObject("Scripting.FileSystemObject")

'Loop through all sheets and create (or overwrite) a csv file for each

For i = 1 To Sheets.Count

Sheets(i).Activate

csvname = ActiveSheet.Name

ext = fs.GetExtensionName(csvname)

If LCase(ext) <> "csv" Then

csvname = csvname & ".csv"

End If

ActiveWorkbook.Save

Fullcsvname = ActiveWorkbook.Path & "\" & csvname

If fs.FileExists(Fullcsvname) Then

fs.Deletefile Fullcsvname

End If

ActiveWorkbook.SaveAs Filename:=Fullcsvname, FileFormat:=xlCSV, CreateBackup:=False

ActiveWorkbook.Close (False)

Workbooks.Open wbook

Next i

Sheets(1).Activate

End Sub

WriteCsvForEachSheet.xls

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...