pandas - SQLCMD--Using the :r Command I have a text file with multiple outputs,is there a way to add a delimiter between outputs or an easy way to split output

Question

I'm using the :r command to running multiple sql scripts in the same session. Each script outputs multiple rows and columns. So by time the script is complete, I am left with a text file with multiple dataframes. Is there a way to easily split this text file into multiple text files, or some way to add a delimiter between the datasets? I have a delimiter added, but it's between columns. The command I am running is sqlcmd -S DATABASE_CONNECTION -m 1 -s "\t" -i sql_testing.sql > C:\Users\USER\file.txt. Within sql_testing I am using the :r command to run a few different scripts. I'm stuck on how to extract these multiple datasets from the text file. I'm not sure if it is something that can be done with pandas or from the command line.

Edit:

The output from the SQL scripts into a single text file looks like this:

ActDate                               \PLACE_NUMBER                                                                                                                                                                                      
--------------------------------------\--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
           2011-08-22 00:00:00.0000000\234                                                                                                                                                                                              
           2011-08-22 00:00:00.0000000\235                                                                                                                                                                                            
           2011-08-22 00:00:00.0000000\236                                                                                                                                                                                              
           2011-08-22 00:00:00.0000000\237                                                                                                                                                                                            
           2011-08-22 00:00:00.0000000\238                                                                                                                                                                                               
           2011-08-22 00:00:00.0000000\239                                                                                                                                                                                            
           2011-08-22 00:00:00.0000000\240                                                                                                                                                                                               
           2011-08-22 00:00:00.0000000\241                                                                                                                                                                                            
           2011-08-22 00:00:00.0000000\242                                                                                                                                                                                               
           2011-08-22 00:00:00.0000000\243                                                                                                                                                                                               
ActDate                               \PLACE_NUMBER                                                                                                                                                                                      
--------------------------------------\--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
           2013-08-22 00:00:00.0000000\456                                                                                                                                                                                               
           2013-08-22 00:00:00.0000000\457                                                                                                                                                                                            
           2013-08-22 00:00:00.0000000\458                                                                                                                                                                                               
           2013-08-22 00:00:00.0000000\459                                                                                                                                                                                            
           2013-08-22 00:00:00.0000000\460                                                                                                                                                                                               

标签： pandasdataframepowershellsedsqlcmd

Answer 1

This might work for you (GNU csplit):

csplit -z file '/ActDate/' '{*}'

Will split file into files named xxnn where nn is from 00.