search

ucsd-ccbb / qiimp

Web application to collect metadata specifications from an experimenter and produce metadata input files with appropriate constraints
3 stars 7 forks source link

Add to Qiimp tutorial how to convert column headers to qiimp compliant #156

Open adswafford opened 5 years ago

adswafford commented 5 years ago

Need to add a section in the Qiimp help/tutorial on how to convert your headers for existing metadata to work with Qiimp.

  1. Open your metadata file in Excel
  2. Insert a row below the headers
  3. In cell A2 enter the formula: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(A1)," ","_"),"/","per"),"(",""),")",""),"-",""),"?",""),"*","_"),"+","pos")
  4. Copy cell A2 and paste into the rest of the row 2 cells with values in row 1
  5. Select row 2 and copy
  6. Open a new sheet or workbook
  7. Paste-transpose on a empty column
  8. Copy the newly pasted column and paste into Qiimp

@AmandaBirmingham any way that this can be handled inside Qiimp if users want to just copy the first row and then paste it into Qiimp? Or maybe instead of just rejecting a selected file if it doesn't comply with Qiimp, we can instead just import the first row and apply the clean-up to make the names compliant and then dump them in the text box?

adswafford commented 5 years ago

Moving to v1.TBS as a nice to have, will update the tutorial in the meantime.

@stephanieorch could you add a section to the help with the above instructions titled: Converting existing metadata columns to Qiita-compliant names in Excel

adswafford commented 5 years ago

@stephanieorch assigned to you, do these make sense?

I've also updated the formula to deal with a few more special characters and to eliminate hidden whitespace.

Converting existing metadata columns to Qiita-compliant names in Excel (or Google Sheets) If you have already created a preliminary metadata file in Excel (or Google Sheets), these steps will allow you to quickly convert your column headers into Qiita-compliant names that can be pasted into the Custom Fields section of Qiimp.

  1. Open your existing (non-Qiimp) metadata file in Excel (or Google Sheets)
  2. Insert a row just below the headers in row 1
  3. In cell A2 enter the formula: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(trim(LOWER(A1))," ",""),"\",""),"/","per"),"{",""),"}",""),"[",""),"]",""),".",""),":",""),";",""),"(",""),")",""),"?",""),"*",""),"+","pos"),"-","") Note if your hyphen represent a negative symbol, enter "neg" in the last entry where "" is currently.
  4. Copy cell A2 and paste into the rest of the row 2 cells that have column headers above in row 1
  5. Select row 2 and copy it
  6. Open a new sheet or workbook
  7. Right-click or use Paste Special to Paste-transpose on a empty column
  8. Select and copy the newly pasted column and paste into the Custom Field box in the Qiimp web tool: image
stephanieorch commented 5 years ago

Step #3 still doesn't make sense to me. Would someone just copy and paste it (except for when the - means negative)?

By saying existing metadata file are you speaking about a file made from Qiimp or not?

Steps 5-7 you're coping and transposing the row you just created with the equation in 3 and making it into a column?

  1. Pasting that column into a Qiimp part of Qiita? If so where? Or putting it in a Qiimp document? If so where?
adswafford commented 5 years ago

Yes to step 3, I just edited the above to help clarify. What tool were you using to make videos before? It may be good to have a quick gif or video to illustrate.

stephanieorch commented 5 years ago

I was using quicktime player. Yes, a visualization would really help me here.